#!/usr/bin/env bash
# SPDX-License-Identifier: MIT
# SPDX-FileCopyrightText: 2025-2026 Marcus Quinn
# memory/_common.sh - Shared utilities for memory-helper modules
# Sourced by memory-helper.sh; do not execute directly.
#
# Provides: logging, DB wrapper, namespace resolution, init_db, migrate_db,
#           format helpers, dedup helpers, auto-prune, ID generation

# Include guard
[[ -n "${_MEMORY_COMMON_LOADED:-}" ]] && return 0
_MEMORY_COMMON_LOADED=1

#######################################
# Print colored message
#######################################
log_info() { echo -e "${BLUE}[INFO]${NC} $*"; }
log_success() { echo -e "${GREEN}[OK]${NC} $*"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $*"; }
log_error() { echo -e "${RED}[ERROR]${NC} $*" >&2; }

#######################################
# Return the global (non-namespaced) memory DB path
#######################################
global_db_path() {
	echo "$MEMORY_BASE_DIR/memory.db"
	return 0
}

#######################################
# SQLite wrapper: sets busy_timeout on every connection (t135.3)
# busy_timeout is per-connection and must be set each time
#######################################
db() {
	sqlite3 -cmd ".timeout 5000" "$@"
}

#######################################
# Require MEMORY_DB to be resolved before database operations
#######################################
_require_memory_db() {
	if [[ -z "${MEMORY_DB:-}" ]]; then
		log_error "MEMORY_DB is not set"
		return 1
	fi
	return 0
}

#######################################
# Require MEMORY_DIR to be resolved before directory operations
#######################################
_require_memory_dir() {
	if [[ -z "${MEMORY_DIR:-}" ]]; then
		log_error "MEMORY_DIR is not set"
		return 1
	fi
	return 0
}

#######################################
# Resolve namespace to memory directory and DB path
# Sets MEMORY_DIR and MEMORY_DB globals
#######################################
resolve_namespace() {
	local namespace="$1"

	if [[ -z "$namespace" ]]; then
		MEMORY_DIR="$MEMORY_BASE_DIR"
		MEMORY_DB="$MEMORY_DIR/memory.db"
		return 0
	fi

	# Validate namespace name (same rules as runner names)
	if [[ ! "$namespace" =~ ^[a-zA-Z][a-zA-Z0-9_-]*$ ]]; then
		log_error "Invalid namespace: '$namespace' (must start with letter, contain only alphanumeric, hyphens, underscores)"
		return 1
	fi
	if [[ ${#namespace} -gt 40 ]]; then
		log_error "Namespace name too long: '$namespace' (max 40 characters)"
		return 1
	fi

	# shellcheck disable=SC2034 # Used by memory-helper.sh main() and recall.sh/maintenance.sh
	MEMORY_NAMESPACE="$namespace"
	MEMORY_DIR="$MEMORY_BASE_DIR/namespaces/$namespace"
	MEMORY_DB="$MEMORY_DIR/memory.db"
	return 0
}

#######################################
# Create pattern_metadata table (t1095, t1114)
# Single authoritative DDL — called from both init_db() (fresh databases)
# and migrate_db() (existing databases upgrading from pre-t1095 schema).
# Extracted to eliminate DDL duplication flagged in PR #1629 review.
#######################################
_create_pattern_metadata_table() {
	_require_memory_db || return 1

	db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS pattern_metadata (
    id TEXT PRIMARY KEY,
    strategy TEXT DEFAULT 'normal' CHECK(strategy IN ('normal', 'prompt-repeat', 'escalated')),
    quality TEXT DEFAULT NULL CHECK(quality IS NULL OR quality IN ('ci-pass-first-try', 'ci-pass-after-fix', 'needs-human')),
    failure_mode TEXT DEFAULT NULL CHECK(failure_mode IS NULL OR failure_mode IN ('hallucination', 'context-miss', 'incomplete', 'wrong-file', 'timeout')),
    tokens_in INTEGER DEFAULT NULL,
    tokens_out INTEGER DEFAULT NULL,
    estimated_cost REAL DEFAULT NULL
);
EOF
	local rc=$?
	return "$rc"
}

#######################################
# Migration helper: add event_date column to learnings FTS5 table (t188)
# FTS5 doesn't support ALTER TABLE — requires backup, recreate, copy, verify.
# Returns 0 on success (or if column already exists), 1 on failure.
#######################################
_migrate_event_date() {
	local has_event_date
	has_event_date=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM pragma_table_info('learnings') WHERE name='event_date';" 2>/dev/null || echo "0")
	[[ "$has_event_date" != "0" ]] && return 0

	log_info "Migrating database to add event_date and relations..."

	local migrate_backup
	migrate_backup=$(backup_sqlite_db "$MEMORY_DB" "pre-migrate-event-date")
	if [[ $? -ne 0 || -z "$migrate_backup" ]]; then
		log_error "Backup failed for memory migration — aborting"
		return 1
	fi
	log_info "Pre-migration backup: $migrate_backup"

	local pre_count
	pre_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings;" 2>/dev/null || echo "0")

	db "$MEMORY_DB" <<'EOF'
-- Create new FTS5 table with event_date
CREATE VIRTUAL TABLE IF NOT EXISTS learnings_new USING fts5(
    id UNINDEXED,
    session_id UNINDEXED,
    content,
    type,
    tags,
    confidence UNINDEXED,
    created_at UNINDEXED,
    event_date UNINDEXED,
    project_path UNINDEXED,
    source UNINDEXED,
    tokenize='porter unicode61'
);

-- Copy existing data (event_date defaults to created_at for existing entries)
INSERT INTO learnings_new (id, session_id, content, type, tags, confidence, created_at, event_date, project_path, source)
SELECT id, session_id, content, type, tags, confidence, created_at, created_at, project_path, source FROM learnings;

-- Drop old table and rename new
DROP TABLE learnings;
ALTER TABLE learnings_new RENAME TO learnings;

-- Create relations table if not exists
-- Column semantics vary by relation_type:
--   updates: id = new version,    supersedes_id = old version it replaces
--   extends: id = extension,      supersedes_id = memory being extended
--   derives: id = derived memory, supersedes_id = source memory it derives from
-- Composite PK allows fan-in: one memory can derive from multiple sources.
CREATE TABLE IF NOT EXISTS learning_relations (
    id TEXT NOT NULL,
    supersedes_id TEXT,
    relation_type TEXT CHECK(relation_type IN ('updates', 'extends', 'derives')),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, supersedes_id, relation_type)
);
EOF

	local post_count
	post_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings;" 2>/dev/null || echo "0")
	if [[ "$post_count" -lt "$pre_count" ]]; then
		log_error "Memory migration FAILED: row count decreased ($pre_count -> $post_count) — rolling back"
		rollback_sqlite_db "$MEMORY_DB" "$migrate_backup"
		return 1
	fi

	log_success "Database migrated successfully ($pre_count rows preserved)"
	cleanup_sqlite_backups "$MEMORY_DB" 5
	return 0
}

#######################################
# Migration helper: ensure learning_relations and learning_access columns exist
# Covers: learning_relations table (pre-event_date DBs), auto_captured (t058),
# graduated_at (t184).
#######################################
_migrate_learning_access_columns() {
	# Ensure relations table exists (for databases created before this feature)
	# Composite PK allows fan-in: one memory can derive from multiple sources.
	db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS learning_relations (
    id TEXT NOT NULL,
    supersedes_id TEXT,
    relation_type TEXT CHECK(relation_type IN ('updates', 'extends', 'derives')),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, supersedes_id, relation_type)
);
EOF

	# Add auto_captured column to learning_access if missing (t058 migration)
	local has_auto_captured
	has_auto_captured=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM pragma_table_info('learning_access') WHERE name='auto_captured';" 2>/dev/null || echo "0")
	if [[ "$has_auto_captured" == "0" ]]; then
		db "$MEMORY_DB" "ALTER TABLE learning_access ADD COLUMN auto_captured INTEGER DEFAULT 0;" || echo "[WARN] Failed to add auto_captured column (may already exist)" >&2
	fi

	# Add graduated_at column to learning_access if missing (t184 migration)
	local has_graduated
	has_graduated=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM pragma_table_info('learning_access') WHERE name='graduated_at';" 2>/dev/null || echo "0")
	if [[ "$has_graduated" == "0" ]]; then
		db "$MEMORY_DB" "ALTER TABLE learning_access ADD COLUMN graduated_at TEXT DEFAULT NULL;" || echo "[WARN] Failed to add graduated_at column (may already exist)" >&2
	fi
	return 0
}

#######################################
# Migration helper: create pattern_metadata table and backfill (t1095, t1114)
# DDL lives in _create_pattern_metadata_table() (shared with init_db).
#######################################
_migrate_pattern_metadata() {
	# Create pattern_metadata table if missing (t1095 migration)
	# Companion table for pattern records — stores strategy, quality, failure_mode, tokens.
	# DDL lives in _create_pattern_metadata_table() to avoid duplication with init_db().
	local has_pattern_metadata
	has_pattern_metadata=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='pattern_metadata';" 2>/dev/null || echo "0")
	if [[ "$has_pattern_metadata" == "0" ]]; then
		log_info "Creating pattern_metadata table (t1095)..."
		_create_pattern_metadata_table || return 1
		# Backfill existing pattern records with default strategy='normal'
		local pattern_types="$PATTERN_TYPES_SQL"
		local backfill_count
		backfill_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings WHERE type IN ($pattern_types);" 2>/dev/null || echo "0")
		if [[ "$backfill_count" -gt 0 ]]; then
			db "$MEMORY_DB" "INSERT OR IGNORE INTO pattern_metadata (id, strategy) SELECT id, 'normal' FROM learnings WHERE type IN ($pattern_types);"
			log_success "Backfilled $backfill_count existing pattern records into pattern_metadata"
		fi
		log_success "pattern_metadata table created (t1095)"
	fi

	# Add estimated_cost column to pattern_metadata if missing (t1114 migration)
	local has_estimated_cost
	has_estimated_cost=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM pragma_table_info('pattern_metadata') WHERE name='estimated_cost';" 2>/dev/null || echo "0")
	if [[ "$has_estimated_cost" == "0" ]]; then
		db "$MEMORY_DB" "ALTER TABLE pattern_metadata ADD COLUMN estimated_cost REAL DEFAULT NULL;" 2>/dev/null ||
			echo "[WARN] Failed to add estimated_cost column (may already exist)" >&2
	fi
	return 0
}

#######################################
# Migration helper: create learning_entities junction table (t1363.3)
# Links learnings to entities — enables entity-scoped memory queries.
# Supports M:M: a learning can relate to multiple entities.
#######################################
_migrate_learning_entities() {
	local has_learning_entities
	has_learning_entities=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='learning_entities';" 2>/dev/null || echo "0")
	if [[ "$has_learning_entities" == "0" ]]; then
		log_info "Creating learning_entities junction table (t1363.3)..."
		db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS learning_entities (
    learning_id TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    PRIMARY KEY (learning_id, entity_id)
);
CREATE INDEX IF NOT EXISTS idx_learning_entities_entity ON learning_entities(entity_id);
EOF
		log_success "learning_entities junction table created (t1363.3)"
	fi
	return 0
}

#######################################
# Migration helper: create core entity identity tables (t1363.1, part 1/2)
# Part of the conversational memory system (p035).
# Tables: entities, entity_channels.
# Skips if entities table already exists (idempotent guard).
# Caller must also invoke _migrate_entity_interaction_tables().
#######################################
_migrate_entity_tables() {
	local has_entities
	has_entities=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='entities';" 2>/dev/null || echo "0")
	[[ "$has_entities" != "0" ]] && return 0

	log_info "Creating entity memory tables (t1363.1)..."
	db "$MEMORY_DB" <<'EOF'
-- Layer 2: Entity relationship model
CREATE TABLE IF NOT EXISTS entities (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL CHECK(type IN ('person', 'agent', 'service')),
    display_name TEXT DEFAULT NULL,
    aliases TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

-- Cross-channel identity linking
CREATE TABLE IF NOT EXISTS entity_channels (
    entity_id TEXT NOT NULL,
    channel TEXT NOT NULL CHECK(channel IN ('matrix', 'simplex', 'email', 'cli', 'slack', 'discord', 'telegram', 'irc', 'web')),
    channel_id TEXT NOT NULL,
    display_name TEXT DEFAULT NULL,
    confidence TEXT DEFAULT 'suggested' CHECK(confidence IN ('confirmed', 'suggested', 'inferred')),
    verified_at TEXT DEFAULT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    PRIMARY KEY (channel, channel_id),
    FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_entity_channels_entity ON entity_channels(entity_id);
EOF
	log_success "Entity identity tables created (t1363.1 part 1)"
	return 0
}

#######################################
# Migration helper: create entity interaction/profile tables (t1363.1, part 2/2)
# Part of the conversational memory system (p035).
# Tables: interactions, conversations, entity_profiles, capability_gaps,
#         interactions_fts.
# Depends on entities table existing (call after _migrate_entity_tables).
#######################################
_migrate_entity_interaction_tables() {
	db "$MEMORY_DB" <<'EOF'
-- Layer 0: Raw interaction log (immutable, append-only)
CREATE TABLE IF NOT EXISTS interactions (
    id TEXT PRIMARY KEY,
    entity_id TEXT NOT NULL,
    channel TEXT NOT NULL,
    channel_id TEXT DEFAULT NULL,
    conversation_id TEXT DEFAULT NULL,
    direction TEXT NOT NULL DEFAULT 'inbound' CHECK(direction IN ('inbound', 'outbound', 'system')),
    content TEXT NOT NULL,
    metadata TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_interactions_entity ON interactions(entity_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_interactions_conversation ON interactions(conversation_id);
CREATE INDEX IF NOT EXISTS idx_interactions_channel ON interactions(channel, channel_id, created_at DESC);

-- Layer 1: Per-conversation context
CREATE TABLE IF NOT EXISTS conversations (
    id TEXT PRIMARY KEY,
    entity_id TEXT NOT NULL,
    channel TEXT NOT NULL,
    channel_id TEXT DEFAULT NULL,
    topic TEXT DEFAULT '',
    summary TEXT DEFAULT '',
    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'idle', 'closed')),
    interaction_count INTEGER DEFAULT 0,
    first_interaction_at TEXT DEFAULT NULL,
    last_interaction_at TEXT DEFAULT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_conversations_entity ON conversations(entity_id, status);

-- Layer 2: Versioned entity profiles
CREATE TABLE IF NOT EXISTS entity_profiles (
    id TEXT PRIMARY KEY,
    entity_id TEXT NOT NULL,
    profile_key TEXT NOT NULL,
    profile_value TEXT NOT NULL,
    evidence TEXT DEFAULT '',
    confidence TEXT DEFAULT 'medium' CHECK(confidence IN ('high', 'medium', 'low')),
    supersedes_id TEXT DEFAULT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
    FOREIGN KEY (supersedes_id) REFERENCES entity_profiles(id)
);
CREATE INDEX IF NOT EXISTS idx_entity_profiles_entity ON entity_profiles(entity_id, profile_key);
CREATE INDEX IF NOT EXISTS idx_entity_profiles_supersedes ON entity_profiles(supersedes_id);

-- Capability gaps detected from entity interactions
CREATE TABLE IF NOT EXISTS capability_gaps (
    id TEXT PRIMARY KEY,
    entity_id TEXT DEFAULT NULL,
    description TEXT NOT NULL,
    evidence TEXT DEFAULT '',
    frequency INTEGER DEFAULT 1,
    status TEXT DEFAULT 'detected' CHECK(status IN ('detected', 'todo_created', 'resolved', 'wont_fix')),
    todo_ref TEXT DEFAULT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_capability_gaps_status ON capability_gaps(status);

-- FTS5 index for searching interactions
CREATE VIRTUAL TABLE IF NOT EXISTS interactions_fts USING fts5(
    id UNINDEXED,
    entity_id UNINDEXED,
    content,
    channel UNINDEXED,
    created_at UNINDEXED,
    tokenize='porter unicode61'
);
EOF
	log_success "Entity interaction tables created (t1363.1 part 2)"
	return 0
}

#######################################
# Migration helper: create conversation_summaries table and channel index (t1363.2)
# Versioned, immutable conversation summaries with source range references.
#######################################
_migrate_conversation_summaries() {
	local has_conv_summaries
	has_conv_summaries=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='conversation_summaries';" 2>/dev/null || echo "0")
	if [[ "$has_conv_summaries" == "0" ]]; then
		log_info "Creating conversation_summaries table (t1363.2)..."
		db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS conversation_summaries (
    id TEXT PRIMARY KEY,
    conversation_id TEXT NOT NULL,
    summary TEXT NOT NULL,
    source_range_start TEXT NOT NULL,
    source_range_end TEXT NOT NULL,
    source_interaction_count INTEGER DEFAULT 0,
    tone_profile TEXT DEFAULT '{}',
    pending_actions TEXT DEFAULT '[]',
    supersedes_id TEXT DEFAULT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (supersedes_id) REFERENCES conversation_summaries(id)
);
CREATE INDEX IF NOT EXISTS idx_conv_summaries_conv ON conversation_summaries(conversation_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_conv_summaries_supersedes ON conversation_summaries(supersedes_id);
EOF
		log_success "conversation_summaries table created (t1363.2)"
	fi

	# Add channel index to conversations if missing (t1363.2)
	local has_conv_channel_idx
	has_conv_channel_idx=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name='idx_conversations_channel';" 2>/dev/null || echo "0")
	if [[ "$has_conv_channel_idx" == "0" ]]; then
		db "$MEMORY_DB" "CREATE INDEX IF NOT EXISTS idx_conversations_channel ON conversations(channel, status);" 2>/dev/null || true
	fi
	return 0
}

#######################################
# Migration helper: add usefulness_score to learning_access (retrieval feedback loop)
# Inspired by Ori Mnemos Q-value system — simplified for operational use.
# Tracks whether recalled memories led to downstream actions (commits, PRs,
# new memories). Memories that prove useful rank higher in future recalls.
# Score range: 0.0 (never useful) to uncapped positive (frequently useful).
# Default 0.0 for existing entries — neutral until feedback is recorded.
#######################################
_migrate_usefulness_score() {
	local has_usefulness
	has_usefulness=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM pragma_table_info('learning_access') WHERE name='usefulness_score';" 2>/dev/null || echo "0")
	if [[ "$has_usefulness" == "0" ]]; then
		db "$MEMORY_DB" "ALTER TABLE learning_access ADD COLUMN usefulness_score REAL DEFAULT 0.0;" 2>/dev/null ||
			echo "[WARN] Failed to add usefulness_score column (may already exist)" >&2
	fi
	return 0
}

#######################################
# Migration helper: create memory_consolidations table (t1413)
# Stores cross-memory consolidation insights generated by LLM analysis.
# Connections between memories are also recorded in learning_relations as 'derives'.
#######################################
_migrate_memory_consolidations() {
	local has_consolidations
	has_consolidations=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='memory_consolidations';" 2>/dev/null || echo "0")
	if [[ "$has_consolidations" == "0" ]]; then
		log_info "Creating memory_consolidations table (t1413)..."
		db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS memory_consolidations (
    id TEXT PRIMARY KEY,
    source_ids TEXT NOT NULL,
    insight TEXT NOT NULL,
    connections TEXT NOT NULL DEFAULT '[]',
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_consolidations_created ON memory_consolidations(created_at DESC);
EOF
		log_success "memory_consolidations table created (t1413)"
	fi
	return 0
}

#######################################
# Rebuild learning_relations when the CHECK constraint predates debunks.
#######################################
_migrate_learning_relations_debunks() {
	local relation_sql
	relation_sql=$(db "$MEMORY_DB" "SELECT sql FROM sqlite_master WHERE type='table' AND name='learning_relations';" 2>/dev/null || echo "")
	if [[ -z "$relation_sql" || "$relation_sql" == *"debunks"* ]]; then
		return 0
	fi

	log_info "Rebuilding learning_relations to allow debunks relation..."
	db "$MEMORY_DB" <<'EOF'
BEGIN TRANSACTION;
ALTER TABLE learning_relations RENAME TO learning_relations_old;
CREATE TABLE learning_relations (
    id TEXT NOT NULL,
    supersedes_id TEXT,
    relation_type TEXT CHECK(relation_type IN ('updates', 'extends', 'derives', 'debunks')),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, supersedes_id, relation_type)
);
INSERT OR IGNORE INTO learning_relations (id, supersedes_id, relation_type, created_at)
SELECT id, supersedes_id, relation_type, created_at
FROM learning_relations_old
WHERE relation_type IN ('updates', 'extends', 'derives', 'debunks');
DROP TABLE learning_relations_old;
COMMIT;
EOF
	log_success "learning_relations now supports debunks"
	return 0
}

#######################################
# Create append-only truth-maintenance events table.
#######################################
_create_learning_truth_events_table() {
	db "$MEMORY_DB" <<'EOF'
CREATE TABLE IF NOT EXISTS learning_truth_events (
    event_id TEXT PRIMARY KEY,
    memory_id TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('live', 'debunked', 'retracted')),
    evidence TEXT,
    replacement_id TEXT,
    debunked_by TEXT,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_learning_truth_memory_created ON learning_truth_events(memory_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_learning_truth_status ON learning_truth_events(status);
EOF
	return 0
}

#######################################
# Migrate truth-maintenance event schema.
#######################################
_migrate_learning_truth_events() {
	local has_truth_events
	has_truth_events=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='learning_truth_events';" 2>/dev/null || echo "0")
	if [[ "$has_truth_events" == "0" ]]; then
		log_info "Creating learning_truth_events table (truth maintenance)..."
		_create_learning_truth_events_table
		log_success "learning_truth_events table created"
	fi
	return 0
}

#######################################
# Migrate existing database to new schema
# With backup-before-modify pattern (t188)
# Note: t311.4 resolved duplicate migrate_db() — this is the single
# authoritative version with t188 backup/rollback safety.
# Each migration step is delegated to a focused helper (_migrate_*).
#######################################
migrate_db() {
	_migrate_event_date || return 1
	_migrate_learning_access_columns
	_migrate_pattern_metadata
	_migrate_learning_entities
	_migrate_entity_tables
	_migrate_entity_interaction_tables
	_migrate_conversation_summaries
	_migrate_memory_consolidations
	_migrate_usefulness_score
	_migrate_learning_relations_debunks
	_migrate_learning_truth_events
	return 0
}

#######################################
# Format JSON results as text (jq fallback)
# Uses jq if available, otherwise basic parsing
#######################################
format_results_text() {
	local input
	input=$(cat)

	if [[ -z "$input" || "$input" == "[]" ]]; then
		return 0
	fi

	if command -v jq &>/dev/null; then
		echo "$input" | jq -r '.[] | "[\(.type)] (\(.confidence)) - Score: \(.score // "N/A" | tostring | .[0:6])\(if (.usefulness_score // 0) != 0 then " | Useful: \(.usefulness_score)" else "" end)\n  \(.content)\n  Tags: \(.tags)\n  Created: \(.created_at) | Accessed: \(.access_count)x\n"' 2>/dev/null
	else
		# Basic fallback without jq - parse JSON manually
		echo "$input" | sed 's/},{/}\n{/g' | while read -r line; do
			local type content tags created access_count
			type=$(echo "$line" | sed -n 's/.*"type":"\([^"]*\)".*/\1/p')
			content=$(echo "$line" | sed -n 's/.*"content":"\([^"]*\)".*/\1/p' | head -c 100)
			tags=$(echo "$line" | sed -n 's/.*"tags":"\([^"]*\)".*/\1/p')
			created=$(echo "$line" | sed -n 's/.*"created_at":"\([^"]*\)".*/\1/p')
			access_count=$(echo "$line" | sed -n 's/.*"access_count":\([0-9]*\).*/\1/p')
			[[ -n "$type" ]] && echo "[$type] $content..."
			[[ -n "$tags" ]] && echo "  Tags: $tags | Created: $created | Accessed: ${access_count:-0}x"
			echo ""
		done
	fi
}

#######################################
# Extract IDs from JSON (jq fallback)
#######################################
extract_ids_from_json() {
	local input
	input=$(cat)

	if command -v jq &>/dev/null; then
		echo "$input" | jq -r '.[].id' 2>/dev/null
	else
		# Basic fallback - extract id values
		echo "$input" | grep -o '"id":"[^"]*"' | sed 's/"id":"//g; s/"//g'
	fi
	return 0
}

#######################################
# Create the core memory schema for fresh databases
#######################################
_create_memory_core_schema() {
	_require_memory_db || return 1

	db "$MEMORY_DB" <<'EOF'
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;

-- FTS5 virtual table for searchable content
-- Note: FTS5 doesn't support foreign keys, so relationships are tracked separately
CREATE VIRTUAL TABLE IF NOT EXISTS learnings USING fts5(
    id UNINDEXED,
    session_id UNINDEXED,
    content,
    type,
    tags,
    confidence UNINDEXED,
    created_at UNINDEXED,
    event_date UNINDEXED,
    project_path UNINDEXED,
    source UNINDEXED,
    tokenize='porter unicode61'
);

-- Separate table for access tracking and metadata (FTS5 doesn't support UPDATE)
-- usefulness_score: retrieval feedback loop — tracks whether recalled memories
-- led to downstream actions. Higher score = more useful in practice.
CREATE TABLE IF NOT EXISTS learning_access (
    id TEXT PRIMARY KEY,
    last_accessed_at TEXT,
    access_count INTEGER DEFAULT 0,
    auto_captured INTEGER DEFAULT 0,
    usefulness_score REAL DEFAULT 0.0
);

-- Relational versioning table (inspired by Supermemory)
-- Tracks how memories relate to each other over time.
-- Column semantics vary by relation_type:
--   updates: id = new version,    supersedes_id = old version it replaces
--   extends: id = extension,      supersedes_id = memory being extended
--   derives: id = derived memory, supersedes_id = source memory it derives from
-- Composite PK allows fan-in: one memory can derive from multiple sources.
CREATE TABLE IF NOT EXISTS learning_relations (
    id TEXT NOT NULL,
    supersedes_id TEXT,
    relation_type TEXT CHECK(relation_type IN ('updates', 'extends', 'derives', 'debunks')),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, supersedes_id, relation_type)
);

-- Append-only truth-maintenance events. FTS5 rows stay immutable; this table
-- records validity changes such as debunking/retraction with evidence.
CREATE TABLE IF NOT EXISTS learning_truth_events (
    event_id TEXT PRIMARY KEY,
    memory_id TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('live', 'debunked', 'retracted')),
    evidence TEXT,
    replacement_id TEXT,
    debunked_by TEXT,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_learning_truth_memory_created ON learning_truth_events(memory_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_learning_truth_status ON learning_truth_events(status);

-- Learning-entity junction table (t1363.3) — links learnings to entities
-- Enables entity-scoped memory queries (e.g., "what do I know about this person?")
CREATE TABLE IF NOT EXISTS learning_entities (
    learning_id TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    PRIMARY KEY (learning_id, entity_id)
);
CREATE INDEX IF NOT EXISTS idx_learning_entities_entity ON learning_entities(entity_id);

-- Memory consolidations (t1413) — cross-memory insight generation
-- Stores synthesized insights from LLM analysis of related memories
CREATE TABLE IF NOT EXISTS memory_consolidations (
    id TEXT PRIMARY KEY,
    source_ids TEXT NOT NULL,
    insight TEXT NOT NULL,
    connections TEXT NOT NULL DEFAULT '[]',
    created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE INDEX IF NOT EXISTS idx_consolidations_created ON memory_consolidations(created_at DESC);
EOF
	local rc=$?
	return "$rc"
}

#######################################
# Create a fresh memory database and companion tables
#######################################
_create_memory_db() {
	_require_memory_db || return 1

	log_info "Creating memory database at $MEMORY_DB"
	_create_memory_core_schema || return 1
	# Extended pattern metadata (t1095, t1114) — companion table for pattern records.
	# DDL is in _create_pattern_metadata_table() (single source of truth, also used by migrate_db).
	_create_pattern_metadata_table || return 1
	log_success "Database initialized with relational versioning support"
	return 0
}

#######################################
# Ensure WAL mode for existing databases created before t135.3
#######################################
_ensure_memory_wal_mode() {
	_require_memory_db || return 1

	# WAL is persistent but may not be set on pre-existing DBs
	local current_mode
	current_mode=$(db "$MEMORY_DB" "PRAGMA journal_mode;" || echo "")
	if [[ "$current_mode" != "wal" ]]; then
		db "$MEMORY_DB" "PRAGMA journal_mode=WAL;" 2>/dev/null || true
	fi
	return 0
}

#######################################
# Initialize database with FTS5 schema
#######################################
init_db() {
	_require_memory_dir || return 1
	_require_memory_db || return 1

	mkdir -p "$MEMORY_DIR" || return 1

	if [[ ! -f "$MEMORY_DB" ]]; then
		_create_memory_db || return 1
	else
		# Migrate existing database if needed
		migrate_db || return 1
	fi

	_ensure_memory_wal_mode || return 1

	return 0
}

#######################################
# Normalize content for deduplication comparison
# Lowercases, strips extra whitespace, removes punctuation
#######################################
normalize_content() {
	local text="$1"
	# Lowercase, collapse whitespace, strip leading/trailing, remove punctuation
	echo "$text" | tr '[:upper:]' '[:lower:]' | tr -s '[:space:]' ' ' | sed 's/^[[:space:]]*//;s/[[:space:]]*$//' | tr -d '[:punct:]'
	return 0
}

#######################################
# Check for duplicate memory before storing
# Uses a three-tier approach (t1363.6):
#   1. Exact content match (cheapest, instant)
#   2. Normalized content match (catches whitespace/punctuation/case)
#   3. Semantic similarity via existing embeddings (catches paraphrases)
# Returns 0 if duplicate found (with ID on stdout), 1 if no duplicate
#######################################
check_duplicate() {
	local content="$1"
	local type="$2"

	# 1. Exact content match (same type)
	local escaped_content="${content//"'"/"''"}"
	local exact_id
	exact_id=$(db "$MEMORY_DB" "SELECT id FROM learnings WHERE content = '$escaped_content' AND type = '$type' LIMIT 1;" 2>/dev/null || echo "")
	if [[ -n "$exact_id" ]]; then
		echo "$exact_id"
		return 0
	fi

	# 2. Normalized content match (catches whitespace/punctuation/case differences)
	local normalized
	normalized=$(normalize_content "$content")
	local escaped_normalized="${normalized//"'"/"''"}"

	# Compare normalized versions of existing entries of the same type
	local norm_id
	norm_id=$(
		db "$MEMORY_DB" <<EOF
SELECT l.id FROM learnings l
WHERE l.type = '$type'
AND replace(replace(replace(replace(replace(lower(l.content),
    '.',''),"'",''),',',''),'!',''),'?','') 
    LIKE '%${escaped_normalized}%'
LIMIT 1;
EOF
	)

	# The LIKE approach above is coarse; refine with a stricter normalized comparison
	# by checking if the normalized stored content equals the normalized new content
	if [[ -z "$norm_id" ]]; then
		# Use FTS5 to find candidates, then compare normalized forms
		local fts_query="${content//"'"/"''"}"
		# Escape embedded double quotes for FTS5
		fts_query="\"${fts_query//\"/\"\"}\""
		local candidates
		candidates=$(db "$MEMORY_DB" "SELECT id, content FROM learnings WHERE learnings MATCH '$fts_query' AND type = '$type' LIMIT 10;" 2>/dev/null || echo "")
		if [[ -n "$candidates" ]]; then
			while IFS='|' read -r cand_id cand_content; do
				[[ -z "$cand_id" ]] && continue
				local cand_normalized
				cand_normalized=$(normalize_content "$cand_content")
				if [[ "$cand_normalized" == "$normalized" ]]; then
					echo "$cand_id"
					return 0
				fi
			done <<<"$candidates"
		fi
	else
		echo "$norm_id"
		return 0
	fi

	# 3. Semantic similarity check via existing embeddings (t1363.6)
	# Catches paraphrased duplicates that differ in wording but convey the same insight.
	# Only runs when embeddings are configured — graceful no-op otherwise.
	local semantic_id
	semantic_id=$(check_semantic_duplicate "$content" "$type")
	if [[ -n "$semantic_id" ]]; then
		echo "$semantic_id"
		return 0
	fi

	return 1
}

#######################################
# Check for semantically similar duplicates using existing embeddings (t1363.6)
# Uses the embeddings engine to find memories with cosine similarity >= 0.92
# (high threshold to avoid false positives — only catches clear paraphrases).
# Returns matching ID on stdout if found, empty otherwise.
# Gracefully returns empty if embeddings are not configured.
#######################################
check_semantic_duplicate() {
	local content="$1"
	local type="$2"

	# Locate the embeddings helper relative to this script
	local embeddings_script
	embeddings_script="$(dirname "${BASH_SOURCE[0]}")/../memory-embeddings-helper.sh"

	# Bail if embeddings not available
	if [[ ! -x "$embeddings_script" ]]; then
		return 1
	fi

	# Check if embeddings are configured (config file exists)
	local config_file="${MEMORY_BASE_DIR}/.embeddings-config"
	if [[ ! -f "$config_file" ]]; then
		return 1
	fi

	# Check if embeddings DB exists
	local embeddings_db="${MEMORY_DIR}/embeddings.db"
	if [[ ! -f "$embeddings_db" ]]; then
		return 1
	fi

	# Search for semantically similar memories (limit to 3 candidates)
	local ns_args=()
	if [[ -n "${MEMORY_NAMESPACE:-}" ]]; then
		ns_args=("--namespace" "$MEMORY_NAMESPACE")
	fi

	local search_result
	search_result=$("$embeddings_script" "${ns_args[@]}" search "$content" --limit 3 --json 2>/dev/null || echo "")

	if [[ -z "$search_result" || "$search_result" == "[]" ]]; then
		return 1
	fi

	# Parse results: look for same-type entries with similarity >= 0.92
	# High threshold ensures we only catch clear paraphrases, not merely related content
	local semantic_threshold="0.92"
	local match_id
	if command -v jq &>/dev/null; then
		match_id=$(echo "$search_result" | jq -r \
			--arg type "$type" \
			--arg threshold "$semantic_threshold" \
			'[.[] | select(.type == $type and (.score | tonumber) >= ($threshold | tonumber))] | first | .id // empty' \
			2>/dev/null || echo "")
	else
		# Fallback: use python for JSON parsing
		match_id=$(python3 -c "
import json, sys
try:
    results = json.loads(sys.stdin.read())
    for r in results:
        if r.get('type') == '$type' and float(r.get('score', 0)) >= $semantic_threshold:
            print(r['id'])
            break
except (json.JSONDecodeError, ValueError, KeyError, TypeError) as e:
    print(f'Semantic duplicate check: parse error: {e}', file=sys.stderr)
" <<<"$search_result" || echo "")
	fi

	if [[ -n "$match_id" ]]; then
		echo "$match_id"
		return 0
	fi

	return 1
}

#######################################
# Auto-prune stale entries (called opportunistically on store)
# Only runs if last prune was >24h ago, to avoid overhead on every store.
#
# Entity-relevance-aware pruning (t1363.6):
#   Instead of a fixed DEFAULT_MAX_AGE_DAYS cutoff for all entries, the prune
#   logic now considers entity relationships:
#   - Entries linked to entities (via learning_entities) are preserved 3x longer
#     because they represent relationship knowledge that compounds over time.
#   - Unlinked entries still use the base threshold (DEFAULT_MAX_AGE_DAYS).
#   - Entries that have been accessed are never auto-pruned (unchanged).
#######################################
auto_prune() {
	local prune_marker="$MEMORY_DIR/.last_auto_prune"
	local prune_interval_seconds=86400 # 24 hours

	# Check if we should run
	if [[ -f "$prune_marker" ]]; then
		local last_prune
		last_prune=$(_file_mtime_epoch "$prune_marker")
		local now
		now=$(date +%s)
		local elapsed=$((now - last_prune))
		if [[ "$elapsed" -lt "$prune_interval_seconds" ]]; then
			return 0
		fi
	fi

	# Entity-linked entries get 3x the retention period (e.g., 270 days vs 90)
	# because relationship knowledge compounds over time and is harder to re-derive.
	local entity_retention_multiplier=3
	local entity_max_age_days=$((DEFAULT_MAX_AGE_DAYS * entity_retention_multiplier))

	# Check if learning_entities table exists (may not on older DBs before t1363.3)
	local has_learning_entities
	has_learning_entities=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='learning_entities';" 2>/dev/null || echo "0")

	if [[ "$has_learning_entities" == "1" ]]; then
		# Two-tier pruning: unlinked entries at base threshold, entity-linked at extended threshold

		# Tier 1: Prune unlinked entries older than DEFAULT_MAX_AGE_DAYS, never accessed
		local unlinked_stale_count
		unlinked_stale_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings l LEFT JOIN learning_access a ON l.id = a.id LEFT JOIN learning_entities le ON l.id = le.learning_id WHERE l.created_at < datetime('now', '-$DEFAULT_MAX_AGE_DAYS days') AND a.id IS NULL AND le.learning_id IS NULL;" 2>/dev/null || echo "0")

		if [[ "$unlinked_stale_count" -gt 0 ]]; then
			local unlinked_subquery="SELECT l.id FROM learnings l LEFT JOIN learning_access a ON l.id = a.id LEFT JOIN learning_entities le ON l.id = le.learning_id WHERE l.created_at < datetime('now', '-$DEFAULT_MAX_AGE_DAYS days') AND a.id IS NULL AND le.learning_id IS NULL"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE id IN ($unlinked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE supersedes_id IN ($unlinked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_access WHERE id IN ($unlinked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learnings WHERE id IN ($unlinked_subquery);"
			log_info "Auto-pruned $unlinked_stale_count unlinked stale entries (>$DEFAULT_MAX_AGE_DAYS days, never accessed)"
		fi

		# Tier 2: Prune entity-linked entries older than extended threshold, never accessed
		local linked_stale_count
		linked_stale_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings l LEFT JOIN learning_access a ON l.id = a.id INNER JOIN learning_entities le ON l.id = le.learning_id WHERE l.created_at < datetime('now', '-$entity_max_age_days days') AND a.id IS NULL;" 2>/dev/null || echo "0")

		if [[ "$linked_stale_count" -gt 0 ]]; then
			local linked_subquery="SELECT l.id FROM learnings l LEFT JOIN learning_access a ON l.id = a.id INNER JOIN learning_entities le ON l.id = le.learning_id WHERE l.created_at < datetime('now', '-$entity_max_age_days days') AND a.id IS NULL"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_entities WHERE learning_id IN ($linked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE id IN ($linked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE supersedes_id IN ($linked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_access WHERE id IN ($linked_subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learnings WHERE id IN ($linked_subquery);"
			log_info "Auto-pruned $linked_stale_count entity-linked stale entries (>$entity_max_age_days days, never accessed)"
		fi

		local total_pruned=$((unlinked_stale_count + linked_stale_count))
		if [[ "$total_pruned" -gt 0 ]]; then
			db_cleanup "$MEMORY_DB" "INSERT INTO learnings(learnings) VALUES('rebuild');"
		fi
	else
		# Fallback: no entity tables — use original fixed-threshold pruning
		local stale_count
		stale_count=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM learnings l LEFT JOIN learning_access a ON l.id = a.id WHERE l.created_at < datetime('now', '-$DEFAULT_MAX_AGE_DAYS days') AND a.id IS NULL;" 2>/dev/null || echo "0")

		if [[ "$stale_count" -gt 0 ]]; then
			local subquery="SELECT l.id FROM learnings l LEFT JOIN learning_access a ON l.id = a.id WHERE l.created_at < datetime('now', '-$DEFAULT_MAX_AGE_DAYS days') AND a.id IS NULL"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE id IN ($subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE supersedes_id IN ($subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learning_access WHERE id IN ($subquery);"
			db_cleanup "$MEMORY_DB" "DELETE FROM learnings WHERE id IN ($subquery);"
			db_cleanup "$MEMORY_DB" "INSERT INTO learnings(learnings) VALUES('rebuild');"
			log_info "Auto-pruned $stale_count stale entries (>$DEFAULT_MAX_AGE_DAYS days, never accessed)"
		fi
	fi

	# Update marker
	touch "$prune_marker"
	return 0
}

#######################################
# Validate that an entity_id exists in the entities table
# Returns 0 if valid, 1 if not found
#######################################
validate_entity_id() {
	local entity_id="$1"
	local escaped_id="${entity_id//"'"/"''"}"
	local exists
	exists=$(db "$MEMORY_DB" "SELECT COUNT(*) FROM entities WHERE id = '$escaped_id';" 2>/dev/null || echo "0")
	if [[ "$exists" == "0" ]]; then
		return 1
	fi
	return 0
}

#######################################
# Link a learning to an entity in the junction table
#######################################
link_learning_entity() {
	local learning_id="$1"
	local entity_id="$2"
	local escaped_learning="${learning_id//"'"/"''"}"
	local escaped_entity="${entity_id//"'"/"''"}"
	db "$MEMORY_DB" <<EOF
INSERT OR IGNORE INTO learning_entities (learning_id, entity_id)
VALUES ('$escaped_learning', '$escaped_entity');
EOF
	return 0
}

#######################################
# Run a cleanup SQL statement, logging errors instead of suppressing them.
# Used for secondary operations (relation/access cleanup) where failure
# should not abort the parent operation but must not be silently swallowed.
# Usage: db_cleanup "$MEMORY_DB" "DELETE FROM learning_relations WHERE ..."
#######################################
db_cleanup() {
	local db_path="$1"
	shift
	local err_output
	if ! err_output=$(db "$db_path" "$@" 2>&1); then
		log_warn "Cleanup SQL failed: $err_output (statement: ${*:0:120})"
	fi
}

#######################################
# Generate unique ID
#######################################
generate_id() {
	# Use timestamp + random for uniqueness
	echo "mem_$(date +%Y%m%d%H%M%S)_$(head -c 4 /dev/urandom | xxd -p)"
	return 0
}
