package knowledgeqa

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/rewind"
)

// DeleteSessionConversationForOwner makes owner deletion authoritative: the
// session row cascades attempts, events, citations, evidence metadata, and
// turns, while the canonical Human-visible message lifecycle deletes the
// corresponding thread.
func (store *Store) DeleteSessionConversationForOwner(ctx context.Context, workspaceID, ownerUserID, threadChannelID string, now time.Time) (Session, error) {
	if store == nil || store.db == nil {
		return Session{}, fmt.Errorf("knowledge Q&A store is not configured")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	ownerUserID = strings.TrimSpace(ownerUserID)
	threadChannelID = strings.TrimSpace(threadChannelID)
	now = normalizeNow(now)
	if workspaceID == "" || ownerUserID == "" || threadChannelID == "" {
		return Session{}, ErrNotFound
	}
	var deleted Session
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		session, err := scanSession(tx.QueryRow(ctx, sessionSelectSQL()+`
WHERE workspace_id=$1 AND owner_user_id=$2 AND thread_channel_id=$3
FOR UPDATE
`, workspaceID, ownerUserID, threadChannelID))
		deleteSessionRow := true
		if errors.Is(err, ErrNotFound) {
			session, err = lockLegacySessionConversationForOwner(ctx, tx, workspaceID, ownerUserID, threadChannelID)
			deleteSessionRow = false
		}
		if err != nil {
			return err
		}
		deleted = session
		if session.ActiveTurnID != "" {
			if _, err := tx.Exec(ctx, `
UPDATE knowledge_qa_turns
SET status='canceled', assistant_message_id='', debug_model_output=NULL,
    completed_at=$2, updated_at=$2
WHERE id=$1 AND status IN ('queued','preparing','generating')
`, session.ActiveTurnID, now); err != nil {
				return fmt.Errorf("cancel deleted Knowledge Q&A turn: %w", err)
			}
			if _, err := tx.Exec(ctx, `
UPDATE knowledge_qa_turn_attempts AS attempt
JOIN knowledge_qa_turns AS turn ON turn.current_attempt_id=attempt.id
SET attempt.status='canceled', attempt.completed_at=$2, attempt.updated_at=$2
WHERE turn.id=$1
`, session.ActiveTurnID, now); err != nil {
				return fmt.Errorf("cancel deleted Knowledge Q&A attempt: %w", err)
			}
		}
		if err := deleteConversationMessages(ctx, tx, session, now); err != nil {
			return err
		}
		if !deleteSessionRow {
			return nil
		}
		result, err := tx.Exec(ctx, `
DELETE FROM knowledge_qa_sessions
WHERE workspace_id=$1 AND owner_user_id=$2 AND id=$3
`, workspaceID, ownerUserID, session.ID)
		if err != nil {
			return fmt.Errorf("delete Knowledge Q&A session: %w", err)
		}
		if result.RowsAffected() != 1 {
			return ErrNotFound
		}
		return nil
	})
	return deleted, err
}

func lockLegacySessionConversationForOwner(ctx context.Context, queryer coredb.QueryRower, workspaceID, ownerUserID, threadChannelID string) (Session, error) {
	var session Session
	var metadata []byte
	err := queryer.QueryRow(ctx, `
SELECT thread.workspace_id, workspace.organization_id,
       owner.user_id, owner.id,
       thread.root_message_id, thread.id,
       COALESCE(root.metadata, '{}')
FROM scopes AS thread
JOIN scopes AS parent
  ON parent.workspace_id=thread.workspace_id
 AND parent.id=thread.parent_scope_id
 AND parent.type='channel'
JOIN scope_channel_profiles AS parent_profile
  ON parent_profile.workspace_id=parent.workspace_id
 AND parent_profile.scope_id=parent.id
JOIN scope_messages AS root
  ON root.workspace_id=thread.workspace_id
 AND root.scope_id=thread.parent_scope_id
 AND root.id=thread.root_message_id
JOIN members AS owner
  ON owner.workspace_id=thread.workspace_id
 AND owner.id=root.author_member_id
JOIN workspaces AS workspace ON workspace.id=thread.workspace_id
WHERE thread.workspace_id=$1 AND thread.id=$2
  AND thread.type='thread' AND thread.status='active'
  AND parent.status='active' AND parent.archived_at IS NULL
  AND parent_profile.visibility='private'
  AND parent_profile.subsystem=$3
  AND parent_profile.owner_member_id=owner.id
  AND root.author_type='human'
  AND owner.type='human' AND owner.status='active' AND owner.user_id=$4
  AND NOT EXISTS (
    SELECT 1
    FROM scope_memberships AS parent_membership
    JOIN members AS other_human
      ON other_human.id=parent_membership.member_id
     AND other_human.workspace_id=thread.workspace_id
     AND other_human.type='human'
     AND other_human.status='active'
    WHERE parent_membership.scope_id=parent.id
      AND parent_membership.removed_at IS NULL
      AND parent_membership.archived_at IS NULL
      AND parent_membership.effective_to_seq IS NULL
      AND parent_membership.visibility='public'
      AND other_human.id<>root.author_member_id
  )
FOR UPDATE
`, workspaceID, threadChannelID, message.ChannelSubsystemKB, ownerUserID).Scan(
		&session.WorkspaceID, &session.OrganizationID,
		&session.OwnerUserID, &session.OwnerMemberID,
		&session.RootMessageID, &session.ThreadChannelID,
		&metadata,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Session{}, ErrNotFound
		}
		return Session{}, fmt.Errorf("lock legacy Knowledge Q&A conversation: %w", err)
	}
	sessionID, ok, err := legacyKnowledgeQASessionIDFromMetadata(metadata)
	if err != nil {
		return Session{}, err
	}
	if !ok {
		return Session{}, ErrNotFound
	}
	session.ID = sessionID
	session.Status = "active"
	return session, nil
}

func legacyKnowledgeQASessionIDFromMetadata(raw []byte) (string, bool, error) {
	metadata, err := message.DecodeMessageMetadata(raw)
	if err != nil {
		return "", false, fmt.Errorf("decode legacy Knowledge Q&A root metadata: %w", err)
	}
	if knowledgeSession, ok := metadataObject(metadata["knowledgeQASession"]); ok &&
		metadataString(knowledgeSession["kind"]) == "knowledge_qa_session" {
		return metadataString(knowledgeSession["sessionId"]), true, nil
	}
	if agentSession, ok := metadataObject(metadata[message.AgentThreadSessionMetadataKey]); ok &&
		metadataString(agentSession["kind"]) == message.AgentThreadSessionMetadataKind {
		return "", true, nil
	}
	return "", false, nil
}

func metadataObject(value any) (map[string]any, bool) {
	object, ok := value.(map[string]any)
	return object, ok
}

func metadataString(value any) string {
	text, _ := value.(string)
	return strings.TrimSpace(text)
}

func deleteConversationMessages(ctx context.Context, tx coredb.Tx, session Session, now time.Time) error {
	var parentScopeID string
	err := tx.QueryRow(ctx, `
SELECT parent_scope_id FROM scopes
WHERE workspace_id=$1 AND id=$2 AND type='thread'
FOR UPDATE
`, session.WorkspaceID, session.ThreadChannelID).Scan(&parentScopeID)
	switch {
	case err == nil:
		parentScopeID = strings.TrimSpace(parentScopeID)
	case coredb.IsNotFound(err):
		return nil
	default:
		return fmt.Errorf("lock Knowledge Q&A reply thread: %w", err)
	}
	metadata, err := model.MarshalJSONObject("Knowledge Q&A root tombstone", message.BuildMessageRecallMetadata(
		session.OwnerMemberID, now.Format(time.RFC3339Nano),
		"Knowledge Q&A conversation deleted",
	))
	if err != nil {
		return fmt.Errorf("encode Knowledge Q&A root tombstone: %w", err)
	}
	if _, err := message.RecallScopeMessageTx(ctx, tx, message.ScopeRecallMessageRequest{
		WorkspaceID:        session.WorkspaceID,
		ScopeID:            parentScopeID,
		MessageID:          session.RootMessageID,
		RecalledByMemberID: session.OwnerMemberID,
		ExpectedAuthorType: message.ScopeActorHuman,
		ExpectedAuthorID:   session.OwnerMemberID,
		PlaceholderContent: "Knowledge Q&A conversation deleted",
		Metadata:           metadata,
		RecalledAt:         now,
	}); err != nil {
		return fmt.Errorf("recall Knowledge Q&A root message: %w", err)
	}
	messageIDs, err := loadThreadMessageIDs(ctx, tx, session.WorkspaceID, session.ThreadChannelID)
	if err != nil {
		return err
	}
	if err := rewind.DeleteCascadeReplyThread(ctx, tx, session.WorkspaceID, rewind.CascadeReplyThread{
		ScopeID: session.ThreadChannelID, MessageIDs: messageIDs,
	}); err != nil {
		return fmt.Errorf("delete Knowledge Q&A reply thread: %w", err)
	}
	return nil
}

func loadThreadMessageIDs(ctx context.Context, queryer coredb.Queryer, workspaceID, threadChannelID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT id FROM scope_messages
WHERE workspace_id=$1 AND scope_id=$2
ORDER BY seq ASC
`, strings.TrimSpace(workspaceID), strings.TrimSpace(threadChannelID))
	if err != nil {
		return nil, fmt.Errorf("list Knowledge Q&A reply messages: %w", err)
	}
	defer rows.Close()
	messageIDs := make([]string, 0)
	for rows.Next() {
		var messageID string
		if err := rows.Scan(&messageID); err != nil {
			return nil, fmt.Errorf("scan Knowledge Q&A reply message: %w", err)
		}
		if messageID = strings.TrimSpace(messageID); messageID != "" {
			messageIDs = append(messageIDs, messageID)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate Knowledge Q&A reply messages: %w", err)
	}
	return messageIDs, nil
}

func (store *Store) validateSessionThread(ctx context.Context, params CreateSessionParams) error {
	var found int
	if err := store.db.QueryRow(ctx, `
SELECT COUNT(*)
FROM scopes AS thread
JOIN scopes AS parent
  ON parent.workspace_id=thread.workspace_id
 AND parent.id=thread.parent_scope_id
 AND parent.type='channel'
JOIN scope_channel_profiles AS parent_profile
  ON parent_profile.workspace_id=parent.workspace_id
 AND parent_profile.scope_id=parent.id
JOIN scope_messages AS root
  ON root.workspace_id=thread.workspace_id
 AND root.scope_id=thread.parent_scope_id
 AND root.id=thread.root_message_id
JOIN workspaces AS workspace ON workspace.id=thread.workspace_id
WHERE thread.workspace_id=$1 AND thread.id=$2
  AND thread.type='thread' AND thread.status='active'
  AND thread.root_message_id=$3
  AND root.author_type='human' AND root.author_member_id=$4
  AND workspace.organization_id=$5
  AND parent.status='active' AND parent.archived_at IS NULL
  AND parent_profile.visibility='private'
  AND parent_profile.subsystem=$6
  AND parent_profile.owner_member_id=$7
  AND NOT EXISTS (
    SELECT 1
    FROM scope_memberships AS parent_membership
    JOIN members AS other_human
      ON other_human.id=parent_membership.member_id
     AND other_human.workspace_id=thread.workspace_id
     AND other_human.type='human'
     AND other_human.status='active'
    WHERE parent_membership.scope_id=parent.id
      AND parent_membership.removed_at IS NULL
      AND parent_membership.archived_at IS NULL
      AND parent_membership.effective_to_seq IS NULL
      AND parent_membership.visibility='public'
      AND other_human.id<>root.author_member_id
  )
`, params.WorkspaceID, params.ThreadChannelID, params.RootMessageID,
		params.OwnerMemberID, params.OrganizationID,
		message.ChannelSubsystemKB, params.OwnerMemberID).Scan(&found); err != nil {
		return fmt.Errorf("validate Knowledge Q&A reply thread: %w", err)
	}
	if found != 1 {
		return ErrForbidden
	}
	return nil
}

func lockSession(ctx context.Context, tx coredb.Tx, workspaceID, sessionID string) (Session, error) {
	return scanSession(tx.QueryRow(ctx, sessionSelectSQL()+`
WHERE workspace_id=$1 AND id=$2 FOR UPDATE
`, workspaceID, sessionID))
}

func (store *Store) loadSession(ctx context.Context, workspaceID, sessionID string) (Session, error) {
	if store == nil || store.db == nil {
		return Session{}, fmt.Errorf("knowledge Q&A store is not configured")
	}
	return scanSession(store.db.QueryRow(ctx, sessionSelectSQL()+`
WHERE workspace_id=$1 AND id=$2
`, strings.TrimSpace(workspaceID), strings.TrimSpace(sessionID)))
}

func sessionSelectSQL() string {
	return `
SELECT id, workspace_id, organization_id, owner_user_id, owner_member_id,
       root_message_id, thread_channel_id,
       requested_knowledge_base_ids_json, requested_source_ids_json,
       requested_model, requested_reason_level, web_search_enabled,
       active_turn_id, status, last_activity_at, created_at, updated_at
FROM knowledge_qa_sessions
`
}