package sqlite

import (
	"database/sql"
	"fmt"
	"sync"

	log "xbot/logger"
)

// CoreMemoryService handles core memory block CRUD operations.
type CoreMemoryService struct {
	db               *DB
	migrateOnce      sync.Once
	migrateError     error
	personaIsolation bool // if true, persona blocks don't fallback to tenantID=0
}

// SetPersonaIsolation controls whether persona blocks fallback to tenantID=0.
// When true, each tenant must maintain its own persona independently.
func (s *CoreMemoryService) SetPersonaIsolation(enabled bool) {
	s.personaIsolation = enabled
}

// NewCoreMemoryService creates a new core memory service.
func NewCoreMemoryService(db *DB) *CoreMemoryService {
	return &CoreMemoryService{db: db}
}

// DefaultBlocks are the standard core memory blocks with their character limits.
var DefaultBlocks = map[string]int{
	"persona":         2000, // bot's identity / personality
	"human":           2000, // current user observations
	"working_context": 4000, // active working facts / session context
}

// InitBlocks ensures all default blocks exist for a tenant.
// - persona: uses tenantID (per-tenant, each Agent/SubAgent has independent persona)
// - human: uses userID if non-empty, with tenantID=0 (cross-tenant per-user)
// - working_context: uses tenantID (per-tenant)
func (s *CoreMemoryService) InitBlocks(tenantID int64, userID string) error {
	conn := s.db.Conn()

	// Run migration once (no-op if already done)
	s.migrateOnce.Do(func() {
		s.migrateError = s.migrateLegacyData(conn)
	})
	if s.migrateError != nil {
		return fmt.Errorf("migration: %w", s.migrateError)
	}

	for name, limit := range DefaultBlocks {
		effectiveTenantID, uid := resolveBlockKey(tenantID, name, userID)

		_, err := conn.Exec(`
			INSERT OR IGNORE INTO core_memory_blocks (tenant_id, block_name, user_id, char_limit)
			VALUES (?, ?, ?, ?)
		`, effectiveTenantID, name, uid, limit)
		if err != nil {
			return fmt.Errorf("init block %s: %w", name, err)
		}
	}
	return nil
}

// migrateLegacyData migrates legacy core memory data to the new scheme:
// - persona: kept at original tenantID (no longer merged to tenantID=0)
// - human: merge all tenantID's human blocks to tenantID=0 (keep longest per user_id)
func (s *CoreMemoryService) migrateLegacyData(db *sql.DB) error {
	// Check if migration marker exists
	var marker int
	err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='core_memory_migrations'").Scan(&marker)
	if err != nil {
		return err
	}

	// Create migration marker table if not exists
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS core_memory_migrations (
			name TEXT PRIMARY KEY,
			applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		return err
	}

	// Check if migration already applied
	err = db.QueryRow("SELECT 1 FROM core_memory_migrations WHERE name = 'migrate_to_tenant_0'").Scan(&marker)
	if err == nil {
		// Already migrated
		return nil
	}
	if err != sql.ErrNoRows {
		return err
	}

	// NOTE: persona is no longer migrated to tenantID=0
	// Each tenant keeps its own persona at its original tenantID

	// For human: for each user_id, keep longest content from any tenant, insert to tenantID=0
	_, err = db.Exec(`
		INSERT OR REPLACE INTO core_memory_blocks (tenant_id, block_name, user_id, content, char_limit, updated_at)
		SELECT 0, 'human', user_id, content, char_limit, CURRENT_TIMESTAMP
		FROM (
			SELECT user_id, content, char_limit,
				ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY LENGTH(content) DESC) AS rn
			FROM core_memory_blocks
			WHERE block_name = 'human' AND user_id != ''
		)
		WHERE rn = 1
	`)
	if err != nil {
		return fmt.Errorf("migrate human: %w", err)
	}

	// Clean up legacy human data (old tenantID != 0)
	_, err = db.Exec(`
		DELETE FROM core_memory_blocks
		WHERE block_name = 'human' AND tenant_id != 0
	`)
	if err != nil {
		return fmt.Errorf("cleanup legacy human: %w", err)
	}

	// Mark migration as completed (after successful migration)
	_, err = db.Exec(`
		INSERT INTO core_memory_migrations (name)
		VALUES ('migrate_to_tenant_0')
	`)
	if err != nil {
		return fmt.Errorf("mark migration: %w", err)
	}

	log.Info("Core memory migration completed: human blocks merged to tenantID=0, persona kept at original tenantID")
	return nil
}

// resolveBlockKey resolves the effective tenantID and userID for a given block type.
// - persona: per-tenant, uses the passed tenantID directly
// - human: per-user cross-tenant, uses tenantID=0 and the provided userID
// - working_context: per-tenant, uses the passed tenantID directly
func resolveBlockKey(tenantID int64, blockName, userID string) (effectiveTenantID int64, uid string) {
	effectiveTenantID = tenantID
	switch blockName {
	case "persona":
		// per-tenant, use the passed tenantID directly
	case "human":
		effectiveTenantID = 0
		if userID != "" {
			uid = userID
		}
	case "working_context":
		// per-tenant
	}
	return effectiveTenantID, uid
}

// GetBlock reads a single core memory block.
// - persona: uses tenantID (per-tenant, each Agent/SubAgent has independent persona)
// - human: uses userID directly as key (cross-tenant shared)
// - working_context: uses tenantID (per-tenant)
func (s *CoreMemoryService) GetBlock(tenantID int64, blockName string, userID string) (content string, charLimit int, err error) {
	conn := s.db.Conn()

	effectiveTenantID, uid := resolveBlockKey(tenantID, blockName, userID)

	err = conn.QueryRow(
		"SELECT content, char_limit FROM core_memory_blocks WHERE tenant_id = ? AND block_name = ? AND user_id = ?",
		effectiveTenantID, blockName, uid,
	).Scan(&content, &charLimit)
	if err == sql.ErrNoRows {
		// Return defaults for known blocks
		if limit, ok := DefaultBlocks[blockName]; ok {
			charLimit = limit
		} else {
			charLimit = 2000
		}
	} else if err != nil {
		return "", 0, fmt.Errorf("get block %s: %w", blockName, err)
	}

	// Fallback: if persona is empty at current tenantID, try tenantID=0.
	// This handles instances where an older v1 migration merged all persona
	// data into tenantID=0; new code reads from the actual tenantID and
	// would otherwise see empty persona (either ErrNoRows or empty content
	// from InitBlocks' INSERT OR IGNORE).
	// When personaIsolation is enabled, skip fallback — each tenant has its own persona.
	if content == "" && blockName == "persona" && effectiveTenantID != 0 && !s.personaIsolation {
		var fbContent string
		var fbLimit int
		fbErr := conn.QueryRow(
			"SELECT content, char_limit FROM core_memory_blocks WHERE tenant_id = 0 AND block_name = ? AND user_id = ?",
			blockName, uid,
		).Scan(&fbContent, &fbLimit)
		if fbErr == nil && fbContent != "" {
			log.WithFields(log.Fields{
				"tenant_id":  effectiveTenantID,
				"block_name": blockName,
			}).Debug("Persona fallback: read from tenantID=0 (legacy v1 migration data)")
			return fbContent, fbLimit, nil
		}
	}

	return content, charLimit, nil
}

// SetBlock upserts a core memory block.
// - persona: uses tenantID (per-tenant, each Agent/SubAgent has independent persona)
// - human: uses userID directly as key (cross-tenant shared)
// - working_context: uses tenantID (per-tenant)
func (s *CoreMemoryService) SetBlock(tenantID int64, blockName, content string, userID string) error {
	conn := s.db.Conn()

	effectiveTenantID, uid := resolveBlockKey(tenantID, blockName, userID)

	// Get char limit
	_, charLimit, err := s.GetBlock(effectiveTenantID, blockName, uid)
	if err != nil {
		return err
	}
	if len(content) > charLimit {
		return fmt.Errorf("content length %d exceeds block %q char_limit %d", len(content), blockName, charLimit)
	}

	// user_id is TEXT NOT NULL DEFAULT '', so ON CONFLICT works correctly
	_, err = conn.Exec(`
		INSERT INTO core_memory_blocks (tenant_id, block_name, user_id, content, char_limit)
		VALUES (?, ?, ?, ?, ?)
		ON CONFLICT(tenant_id, block_name, user_id)
		DO UPDATE SET content = excluded.content, updated_at = CURRENT_TIMESTAMP
	`, effectiveTenantID, blockName, uid, content, charLimit)
	if err != nil {
		return fmt.Errorf("set block %s: %w", blockName, err)
	}

	log.WithFields(log.Fields{
		"tenant_id":  effectiveTenantID,
		"block_name": blockName,
		"user_id":    uid,
		"length":     len(content),
	}).Debug("Core memory block updated")
	return nil
}

// GetAllBlocks reads all core memory blocks.
// - persona: from tenantID (per-tenant, each Agent/SubAgent has independent persona)
// - human: from tenantID=0 with userID (cross-tenant per-user)
// - working_context: from tenantID (per-tenant)
func (s *CoreMemoryService) GetAllBlocks(tenantID int64, userID string) (map[string]string, error) {
	conn := s.db.Conn()

	blocks := make(map[string]string)

	// Get persona from current tenantID (per-tenant)
	var personaContent string
	err := conn.QueryRow(
		"SELECT content FROM core_memory_blocks WHERE tenant_id = ? AND block_name = 'persona' AND user_id = ''",
		tenantID,
	).Scan(&personaContent)
	if err != nil && err != sql.ErrNoRows {
		return nil, fmt.Errorf("query persona: %w", err)
	}
	if err == nil {
		blocks["persona"] = personaContent
	}
	// Fallback: if persona empty/missing at current tenantID, try tenantID=0
	// (handles legacy v1 migration that merged persona into tenantID=0)
	// When personaIsolation is enabled, skip fallback — each tenant has its own persona.
	if blocks["persona"] == "" && tenantID != 0 && !s.personaIsolation {
		var fallbackContent string
		fbErr := conn.QueryRow(
			"SELECT content FROM core_memory_blocks WHERE tenant_id = 0 AND block_name = 'persona' AND user_id = ''",
		).Scan(&fallbackContent)
		if fbErr == nil && fallbackContent != "" {
			blocks["persona"] = fallbackContent
			log.WithField("tenant_id", tenantID).Debug("Persona fallback: read from tenantID=0 in GetAllBlocks")
		}
	}

	// Get working_context from current tenantID
	var wcContent string
	err = conn.QueryRow(
		"SELECT content FROM core_memory_blocks WHERE tenant_id = ? AND block_name = 'working_context' AND user_id = ''",
		tenantID,
	).Scan(&wcContent)
	if err != nil && err != sql.ErrNoRows {
		return nil, fmt.Errorf("query working_context: %w", err)
	}
	if err == nil {
		blocks["working_context"] = wcContent
	}

	// Get human from tenantID=0 with userID (cross-tenant)
	if userID != "" {
		var humanContent string
		err := conn.QueryRow(
			"SELECT content FROM core_memory_blocks WHERE tenant_id = 0 AND block_name = 'human' AND user_id = ?",
			userID,
		).Scan(&humanContent)
		if err != nil && err != sql.ErrNoRows {
			return nil, fmt.Errorf("query human: %w", err)
		}
		if err == nil {
			blocks["human"] = humanContent
		}
	}

	return blocks, nil
}

// ClearBlock clears a single core memory block content for a tenant.
// Uses resolveBlockKey to handle persona/human/working_context correctly.
func (s *CoreMemoryService) ClearBlock(tenantID int64, blockName, userID string) error {
	conn := s.db.Conn()
	effectiveTenantID, uid := resolveBlockKey(tenantID, blockName, userID)
	_, err := conn.Exec(
		"UPDATE core_memory_blocks SET content = '', updated_at = CURRENT_TIMESTAMP WHERE tenant_id = ? AND block_name = ? AND user_id = ?",
		effectiveTenantID, blockName, uid,
	)
	return err
}

// ClearAllBlocks clears all core memory blocks (persona + working_context + human) for a tenant.
// Uses resolveBlockKey to handle persona/human/working_context correctly.
func (s *CoreMemoryService) ClearAllBlocks(tenantID int64, userID string) error {
	conn := s.db.Conn()
	tx, err := conn.Begin()
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	// Clear persona (per-tenant)
	effectivePersonaID, _ := resolveBlockKey(tenantID, "persona", "")
	if _, err := tx.Exec("UPDATE core_memory_blocks SET content = '' WHERE tenant_id = ? AND block_name = 'persona' AND user_id = ''", effectivePersonaID); err != nil {
		return fmt.Errorf("clear persona: %w", err)
	}

	// Clear working_context (per-tenant)
	effectiveWcID, _ := resolveBlockKey(tenantID, "working_context", "")
	if _, err := tx.Exec("UPDATE core_memory_blocks SET content = '' WHERE tenant_id = ? AND block_name = 'working_context' AND user_id = ''", effectiveWcID); err != nil {
		return fmt.Errorf("clear working_context: %w", err)
	}

	// Clear human (cross-tenant by userID)
	effectiveHumanID, humanUID := resolveBlockKey(tenantID, "human", userID)
	if userID != "" {
		if _, err := tx.Exec("UPDATE core_memory_blocks SET content = '' WHERE tenant_id = ? AND block_name = 'human' AND user_id = ?", effectiveHumanID, humanUID); err != nil {
			return fmt.Errorf("clear human: %w", err)
		}
	}

	return tx.Commit()
}
