package onboarding

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

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

const tutorialChannelName = "quick start"

// EnsureForUser idempotently creates the user's private tutorial channel when
// the user is still eligible for the Desktop tutorial flow.
func (store *Store) EnsureForUser(ctx context.Context, userID string, workspaceID string, _ string) (EnsureResult, error) {
	if store == nil || store.db == nil {
		return EnsureResult{}, fmt.Errorf("onboarding store is not configured")
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedUserID == "" || trimmedWorkspaceID == "" {
		return EnsureResult{}, fmt.Errorf("user id and workspace id are required")
	}

	var result EnsureResult
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		existing, err := loadState(ctx, tx, trimmedUserID, trimmedWorkspaceID, true)
		if err == nil {
			result = EnsureResult{State: existing, Eligible: true}
			return nil
		}
		if !errors.Is(err, ErrStateNotFound) {
			return err
		}

		owner, err := loadTutorialOwner(ctx, tx, trimmedUserID, trimmedWorkspaceID)
		if err != nil {
			return err
		}
		usedAgent, err := userHasPreexistingAgentRuntime(ctx, tx, trimmedUserID, trimmedWorkspaceID)
		if err != nil {
			return err
		}
		if usedAgent {
			result = EnsureResult{Eligible: false, EligibilityReason: EligibilityReasonPreexistingAgentRuntime}
			return nil
		}

		state := State{
			UserID:         trimmedUserID,
			WorkspaceID:    trimmedWorkspaceID,
			OrganizationID: owner.organizationID,
			OwnerMemberID:  owner.memberID,
			FlowVersion:    FlowVersionV1,
			Status:         StatusActive,
			CurrentStep:    StepCreateAgent,
		}
		channelID, err := ensureTutorialChannel(ctx, tx, state)
		if err != nil {
			return err
		}
		progressJSON, err := marshalProgress(Progress{})
		if err != nil {
			return err
		}
		now := time.Now().UTC()
		_, err = tx.Exec(ctx, `
INSERT INTO desktop_onboarding_states (
  user_id, workspace_id, organization_id, owner_member_id,
  tutorial_channel_id, flow_version, status, current_step, progress_json,
  created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $10)
ON DUPLICATE KEY UPDATE updated_at = desktop_onboarding_states.updated_at
`, state.UserID, state.WorkspaceID, state.OrganizationID, state.OwnerMemberID,
			channelID, state.FlowVersion, state.Status, state.CurrentStep, progressJSON, now)
		if err != nil {
			return fmt.Errorf("ensure desktop onboarding state: %w", err)
		}
		created, err := loadState(ctx, tx, trimmedUserID, trimmedWorkspaceID, false)
		if err != nil {
			return err
		}
		result = EnsureResult{State: created, Eligible: true}
		return nil
	})
	return result, err
}

func (store *Store) GetForUser(ctx context.Context, userID string, workspaceID string) (State, error) {
	if store == nil || store.db == nil {
		return State{}, fmt.Errorf("onboarding store is not configured")
	}
	return loadState(ctx, store.db, userID, workspaceID, false)
}

func (store *Store) MarkPersonalWorkspaceEligibleInTx(ctx context.Context, tx coredb.Tx, userID string, workspaceID string, organizationID string, ownerMemberID string, now time.Time) error {
	if store == nil || store.db == nil {
		return fmt.Errorf("onboarding store is not configured")
	}
	trimmedUserID := strings.TrimSpace(userID)
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedOrganizationID := strings.TrimSpace(organizationID)
	trimmedOwnerMemberID := strings.TrimSpace(ownerMemberID)
	if trimmedUserID == "" || trimmedWorkspaceID == "" || trimmedOrganizationID == "" || trimmedOwnerMemberID == "" {
		return fmt.Errorf("user id, workspace id, organization id, and owner member id are required")
	}
	if now.IsZero() {
		now = time.Now().UTC()
	}
	state := State{
		UserID:         trimmedUserID,
		WorkspaceID:    trimmedWorkspaceID,
		OrganizationID: trimmedOrganizationID,
		OwnerMemberID:  trimmedOwnerMemberID,
		FlowVersion:    FlowVersionV1,
		Status:         StatusActive,
		CurrentStep:    StepCreateAgent,
	}
	channelID, err := ensureTutorialChannel(ctx, tx, state)
	if err != nil {
		return err
	}
	progressJSON, err := marshalProgress(Progress{})
	if err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO desktop_onboarding_states (
  user_id, workspace_id, organization_id, owner_member_id,
  tutorial_channel_id, flow_version, status, current_step, progress_json,
  created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $10)
ON DUPLICATE KEY UPDATE updated_at = desktop_onboarding_states.updated_at
`, state.UserID, state.WorkspaceID, state.OrganizationID, state.OwnerMemberID,
		channelID, state.FlowVersion, state.Status, state.CurrentStep, progressJSON, now.UTC()); err != nil {
		return fmt.Errorf("mark personal workspace onboarding eligible: %w", err)
	}
	return nil
}

type tutorialOwner struct {
	organizationID string
	memberID       string
}

func loadTutorialOwner(ctx context.Context, queryer coredb.QueryRower, userID string, workspaceID string) (tutorialOwner, error) {
	var owner tutorialOwner
	err := queryer.QueryRow(ctx, `
SELECT w.organization_id, m.id
FROM workspaces w
JOIN members m
  ON m.workspace_id = w.id
 AND m.user_id = $1
 AND m.type = 'human'
 AND m.status = 'active'
WHERE w.id = $2
LIMIT 1
FOR UPDATE
`, userID, workspaceID).Scan(&owner.organizationID, &owner.memberID)
	if err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return tutorialOwner{}, fmt.Errorf("active workspace member not found")
		}
		return tutorialOwner{}, fmt.Errorf("load desktop onboarding owner: %w", err)
	}
	return owner, nil
}

func userHasPreexistingAgentRuntime(ctx context.Context, queryer coredb.QueryRower, userID string, workspaceID string) (bool, error) {
	var exists bool
	err := queryer.QueryRow(ctx, `
SELECT EXISTS(
  SELECT 1
  FROM machines machine
  JOIN agents agent
    ON agent.machine_id = machine.id
   AND agent.workspace_id = machine.workspace_id
   AND agent.status <> 'archived'
  WHERE machine.workspace_id = $1
    AND machine.created_by_user_id = $2
    AND machine.status = 'active'
  LIMIT 1
)
`, workspaceID, userID).Scan(&exists)
	if err != nil {
		return false, fmt.Errorf("check preexisting desktop agent runtime: %w", err)
	}
	return exists, nil
}

func ensureTutorialChannel(ctx context.Context, tx coredb.Tx, state State) (string, error) {
	slug := tutorialSlug(state.UserID, state.WorkspaceID, state.FlowVersion)
	if existing, ok, err := findTutorialChannel(ctx, tx, state.WorkspaceID, state.OwnerMemberID, slug); err != nil {
		return "", err
	} else if ok {
		if err := ensureTutorialChannelMembership(ctx, tx, existing, state.OwnerMemberID); err != nil {
			return "", err
		}
		return existing, nil
	}

	channelID := deterministicChannelID(state.UserID, state.WorkspaceID, state.FlowVersion)
	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
INSERT INTO scopes (
  id, workspace_id, type, parent_scope_id, root_message_id, root_message_short_id,
  identity_key, latest_seq, latest_global_seq, latest_message_at, archived_until_seq,
  status, created_by_member_id, created_at, updated_at, archived_at
) VALUES ($1, $2, 'channel', NULL, NULL, NULL, $3, 0, 0, NULL, 0, 'active', $4, $5, $5, NULL)
ON DUPLICATE KEY UPDATE updated_at = scopes.updated_at
`, channelID, state.WorkspaceID, "channel:"+channelID, state.OwnerMemberID, now); err != nil {
		return "", fmt.Errorf("insert tutorial channel scope: %w", err)
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO scope_channel_profiles (
  scope_id, workspace_id, slug, display_name, description, visibility,
  subsystem, owner_member_id, is_all_channel, created_at, updated_at
) VALUES ($1, $2, $3, $4, '', 'private', 'tutorial', $5, FALSE, $6, $6)
ON DUPLICATE KEY UPDATE updated_at = scope_channel_profiles.updated_at
`, channelID, state.WorkspaceID, slug, tutorialChannelName, state.OwnerMemberID, now); err != nil {
		return "", fmt.Errorf("insert tutorial channel profile: %w", err)
	}
	if existing, ok, err := findTutorialChannel(ctx, tx, state.WorkspaceID, state.OwnerMemberID, slug); err != nil {
		return "", err
	} else if ok {
		channelID = existing
	}
	if err := ensureTutorialChannelMembership(ctx, tx, channelID, state.OwnerMemberID); err != nil {
		return "", err
	}
	return channelID, nil
}

func findTutorialChannel(ctx context.Context, queryer coredb.QueryRower, workspaceID string, ownerMemberID string, slug string) (string, bool, error) {
	var channelID string
	err := queryer.QueryRow(ctx, `
SELECT scope_id
FROM scope_channel_profiles
WHERE workspace_id = $1
  AND slug = $2
  AND subsystem = 'tutorial'
  AND owner_member_id = $3
LIMIT 1
FOR UPDATE
`, workspaceID, slug, ownerMemberID).Scan(&channelID)
	if err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return "", false, nil
		}
		return "", false, fmt.Errorf("find tutorial channel profile: %w", err)
	}
	return channelID, true, nil
}

func ensureTutorialChannelMembership(ctx context.Context, tx coredb.Tx, channelID string, ownerMemberID string) error {
	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
INSERT INTO scope_memberships (
  scope_id, member_id, visibility, owner_member_id, last_read_seq,
  last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
  effective_from_seq, effective_to_seq, authority_from_at, authority_to_at,
  created_at, updated_at, removed_at, archived_at
) VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 1, NULL, LEAST($3, CURRENT_TIMESTAMP(6)), NULL, $3, $3, NULL, NULL)
ON DUPLICATE KEY UPDATE removed_at = NULL,
                        archived_at = NULL,
                        effective_to_seq = NULL,
                        authority_from_at = LEAST(VALUES(authority_from_at), CURRENT_TIMESTAMP(6)),
                        authority_to_at = NULL,
                        updated_at = $3
`, channelID, ownerMemberID, now); err != nil {
		return fmt.Errorf("ensure tutorial channel membership: %w", err)
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO scope_notification_settings (scope_id, member_id, state, created_at, updated_at)
VALUES ($1, $2, 'active', $3, $3)
ON DUPLICATE KEY UPDATE updated_at = scope_notification_settings.updated_at
`, channelID, ownerMemberID, now); err != nil {
		return fmt.Errorf("ensure tutorial channel notification setting: %w", err)
	}
	return nil
}

func loadState(ctx context.Context, queryer coredb.QueryRower, userID string, workspaceID string, forUpdate bool) (State, error) {
	query := `
SELECT user_id, workspace_id, organization_id, owner_member_id,
       tutorial_channel_id, flow_version, status, current_step,
       COALESCE(progress_json, JSON_OBJECT()), completed_at, created_at, updated_at
FROM desktop_onboarding_states
WHERE user_id = $1
  AND workspace_id = $2
  AND flow_version = $3`
	if forUpdate {
		query += " FOR UPDATE"
	}
	var state State
	var progressJSON []byte
	err := queryer.QueryRow(ctx, query, strings.TrimSpace(userID), strings.TrimSpace(workspaceID), FlowVersionV1).Scan(
		&state.UserID, &state.WorkspaceID, &state.OrganizationID, &state.OwnerMemberID,
		&state.TutorialChannelID, &state.FlowVersion, &state.Status, &state.CurrentStep,
		&progressJSON, &state.CompletedAt, &state.CreatedAt, &state.UpdatedAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return State{}, ErrStateNotFound
		}
		return State{}, fmt.Errorf("load desktop onboarding state: %w", err)
	}
	progress, err := unmarshalProgress(progressJSON)
	if err != nil {
		return State{}, err
	}
	state.Progress = progress
	return state, nil
}

func marshalProgress(progress Progress) ([]byte, error) {
	payload := map[string]any{}
	if strings.TrimSpace(progress.ReplyMessageID) != "" {
		payload["replyMessageId"] = strings.TrimSpace(progress.ReplyMessageID)
	}
	if strings.TrimSpace(progress.ReplyScopeID) != "" {
		payload["replyScopeId"] = strings.TrimSpace(progress.ReplyScopeID)
	}
	if progress.ReplySeq > 0 {
		payload["replySeq"] = progress.ReplySeq
	}
	if progress.VisibleThroughSeq > 0 {
		payload["visibleThroughSeq"] = progress.VisibleThroughSeq
	}
	return model.MarshalJSONObject("desktop onboarding progress", payload)
}

func unmarshalProgress(raw []byte) (Progress, error) {
	var payload map[string]any
	if err := model.UnmarshalJSONObject("desktop onboarding progress", raw, &payload); err != nil {
		return Progress{}, err
	}
	var progress Progress
	progress.ReplyMessageID = stringFromJSON(payload["replyMessageId"])
	progress.ReplyScopeID = stringFromJSON(payload["replyScopeId"])
	progress.ReplySeq = int64FromJSON(payload["replySeq"])
	progress.VisibleThroughSeq = int64FromJSON(payload["visibleThroughSeq"])
	return progress, nil
}

func stringFromJSON(value any) string {
	if text, ok := value.(string); ok {
		return strings.TrimSpace(text)
	}
	return ""
}

func int64FromJSON(value any) int64 {
	switch typed := value.(type) {
	case float64:
		return int64(typed)
	case int64:
		return typed
	case int:
		return int64(typed)
	default:
		return 0
	}
}

func tutorialSlug(userID string, workspaceID string, flowVersion string) string {
	sum := sha256.Sum256([]byte("desktop-tutorial-slug\x00" + strings.TrimSpace(userID) + "\x00" + strings.TrimSpace(workspaceID) + "\x00" + strings.TrimSpace(flowVersion)))
	return fmt.Sprintf("quick-start-%x", sum[:8])
}

func deterministicChannelID(userID string, workspaceID string, flowVersion string) string {
	sum := sha256.Sum256([]byte("desktop-tutorial-channel\x00" + strings.TrimSpace(userID) + "\x00" + strings.TrimSpace(workspaceID) + "\x00" + strings.TrimSpace(flowVersion)))
	return "chn_" + hex.EncodeToString(sum[:])[:14]
}