package workspace

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/authidentity"
	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/core/secrets"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/pinyinsearch"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/orgmembership"
	"github.com/tidbcloud/AgentTeam/internal/store/plan"
)

func (store *Store) ListUserWorkspaces(ctx context.Context, userID string) ([]Summary, error) {
	rows, err := store.db.Query(ctx, `
SELECT
    w.id,
    w.organization_id,
    w.slug,
    w.name,
    m.id,
    COALESCE(NULLIF(TRIM(u.display_name), ''), NULLIF(TRIM(m.display_name), ''), m.id) AS member_display_name,
    COALESCE(m.workspace_role, '')
FROM members m
JOIN workspaces w ON w.id = m.workspace_id
JOIN users u ON u.id = m.user_id
WHERE m.user_id = $1 AND m.type = 'human' AND m.status = 'active'
ORDER BY w.created_at ASC
`, userID)
	if err != nil {
		return nil, fmt.Errorf("list user workspaces: %w", err)
	}
	defer rows.Close()

	workspaces := make([]Summary, 0)
	for rows.Next() {
		var summary Summary
		if err := rows.Scan(&summary.WorkspaceID, &summary.OrganizationID, &summary.Slug, &summary.Name, &summary.MemberID, &summary.MemberDisplayName, &summary.WorkspaceRole); err != nil {
			return nil, fmt.Errorf("scan workspace summary: %w", err)
		}
		summary.MemberUserID = userID
		workspaces = append(workspaces, summary)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace summaries: %w", err)
	}
	return workspaces, nil
}

func (store *Store) GetWorkspaceForUser(ctx context.Context, userID string, workspaceID string) (Summary, error) {
	membership, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return Summary{}, err
	}

	row := store.db.QueryRow(ctx, `
SELECT
    w.organization_id,
    w.slug,
    w.name,
    COALESCE(NULLIF(TRIM(u.display_name), ''), NULLIF(TRIM(m.display_name), ''), m.id) AS member_display_name
FROM workspaces w
JOIN members m
  ON m.workspace_id = w.id
 AND m.user_id = $2
 AND m.type = 'human'
 AND m.status = 'active'
JOIN users u ON u.id = m.user_id
WHERE w.id = $1
`, workspaceID, userID)
	summary := Summary{
		WorkspaceID:   workspaceID,
		MemberID:      membership.Member.ID,
		MemberUserID:  userID,
		WorkspaceRole: membership.WorkspaceRole,
	}
	if err := row.Scan(&summary.OrganizationID, &summary.Slug, &summary.Name, &summary.MemberDisplayName); err != nil {
		if coredb.IsNotFound(err) {
			return Summary{}, ErrForbidden
		}
		return Summary{}, fmt.Errorf("load workspace summary: %w", err)
	}
	return summary, nil
}

func workspaceSummaryMemberDisplayName(values ...string) string {
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed != "" {
			return trimmed
		}
	}
	return ""
}

func (store *Store) UpdateWorkspaceNameForUser(ctx context.Context, userID string, workspaceID string, name string) (Summary, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return Summary{}, badRequest("workspaceId is required")
	}
	trimmedName := strings.TrimSpace(name)
	if trimmedName == "" {
		return Summary{}, badRequest("name is required")
	}

	if err := store.requireWorkspaceMemberManagement(ctx, userID, trimmedWorkspaceID); err != nil {
		return Summary{}, err
	}
	if _, err := store.db.Exec(ctx, `
UPDATE workspaces
SET name = $2, updated_at = NOW()
WHERE id = $1
`, trimmedWorkspaceID, trimmedName); err != nil {
		return Summary{}, fmt.Errorf("update workspace name: %w", err)
	}

	return store.GetWorkspaceForUser(ctx, userID, trimmedWorkspaceID)
}

func (store *Store) CreateWorkspaceInvite(ctx context.Context, userID string, workspaceID string, email string, tokenHash string, expiresAt time.Time) (string, error) {
	if err := store.requireWorkspaceMemberManagement(ctx, userID, workspaceID); err != nil {
		return "", err
	}
	normalizedEmail, err := authidentity.NormalizeStrictEmail(email)
	if err != nil {
		return "", err
	}
	inviteID, err := identifiers.NewEntityID("inv")
	if err != nil {
		return "", fmt.Errorf("generate invite id: %w", err)
	}
	_, err = store.db.Exec(ctx, `
	INSERT INTO workspace_invites (id, workspace_id, email, role, token_hash, created_by_user_id, created_at, expires_at)
	VALUES ($1, $2, $3, 'member', $4, $5, NOW(), $6)
	`, inviteID, workspaceID, normalizedEmail, tokenHash, userID, expiresAt)
	if err != nil {
		return "", fmt.Errorf("insert workspace invite: %w", err)
	}
	return inviteID, nil
}

func (store *Store) CreateWorkspaceMobileInvite(ctx context.Context, userID string, workspaceID string, mobile string, tokenHash string, expiresAt time.Time) (string, error) {
	if err := store.requireWorkspaceMemberManagement(ctx, userID, workspaceID); err != nil {
		return "", err
	}
	normalizedMobile, err := authidentity.NormalizeMobile(mobile)
	if err != nil {
		return "", err
	}
	inviteID, err := identifiers.NewEntityID("inv")
	if err != nil {
		return "", fmt.Errorf("generate invite id: %w", err)
	}
	_, err = store.db.Exec(ctx, `
	INSERT INTO workspace_invites (id, workspace_id, mobile, role, token_hash, created_by_user_id, created_at, expires_at)
	VALUES ($1, $2, $3, 'member', $4, $5, NOW(), $6)
	`, inviteID, workspaceID, normalizedMobile, tokenHash, userID, expiresAt)
	if err != nil {
		return "", fmt.Errorf("insert workspace mobile invite: %w", err)
	}
	return inviteID, nil
}

func (store *Store) AcceptWorkspaceInvite(ctx context.Context, user model.User, workspaceID string, token string) (Summary, error) {
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return Summary{}, fmt.Errorf("begin accept invite tx: %w", err)
	}
	defer rollbackTx(tx, ctx)

	tokenHash := secrets.HashToken(token)
	row := tx.QueryRow(ctx, `
SELECT w.id, w.organization_id, w.slug, w.name, i.email, i.mobile, i.created_by_user_id, i.accepted_at, i.revoked_at, i.expires_at
FROM workspace_invites i
JOIN workspaces w ON w.id = i.workspace_id
WHERE i.workspace_id = $1 AND i.token_hash = $2
FOR UPDATE
	`, workspaceID, tokenHash)
	var summary Summary
	var inviteEmail sql.NullString
	var inviteMobile sql.NullString
	var inviterUserID string
	var acceptedAt *time.Time
	var revokedAt *time.Time
	var expiresAt time.Time
	if err := row.Scan(&summary.WorkspaceID, &summary.OrganizationID, &summary.Slug, &summary.Name, &inviteEmail, &inviteMobile, &inviterUserID, &acceptedAt, &revokedAt, &expiresAt); err != nil {
		if coredb.IsNotFound(err) {
			return Summary{}, badRequest("invalid invite token")
		}
		return Summary{}, fmt.Errorf("load invite: %w", err)
	}
	if revokedAt != nil {
		return Summary{}, badRequest("invite revoked")
	}
	if time.Now().UTC().After(expiresAt) {
		return Summary{}, badRequest("invite expired")
	}
	if !workspaceInviteTargetMatchesUser(inviteEmail, inviteMobile, user) {
		return Summary{}, ErrForbidden
	}
	if _, err := orgmembership.EnsureWorkspaceInviteMemberInTx(ctx, tx, orgmembership.WorkspaceInviteMemberParams{
		OrganizationID: summary.OrganizationID,
		UserID:         user.ID,
		InviterUserID:  inviterUserID,
		CheckOrganizationQuotaInTx: func(ctx context.Context, tx coredb.Tx, _ string, metric plan.MetricKey, delta int64) error {
			return store.checkWorkspaceQuotaInTx(ctx, tx, workspaceID, metric, delta)
		},
	}); err != nil {
		return Summary{}, err
	}

	membershipRow := tx.QueryRow(ctx, `
SELECT id, workspace_role, status
FROM members
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human'
`, workspaceID, user.ID)
	var memberID string
	var workspaceRole string
	var memberStatus string
	membershipErr := membershipRow.Scan(&memberID, &workspaceRole, &memberStatus)
	if membershipErr != nil && !coredb.IsNotFound(membershipErr) {
		return Summary{}, fmt.Errorf("load existing workspace membership: %w", membershipErr)
	}
	if coredb.IsNotFound(membershipErr) || memberStatus != "active" {
		hasSeat, err := hasActiveOrganizationSeat(ctx, tx, summary.OrganizationID, user.ID)
		if err != nil {
			return Summary{}, err
		}
		if !hasSeat {
			if err := store.checkWorkspaceQuotaInTx(ctx, tx, workspaceID, plan.MetricSeats, 1); err != nil {
				return Summary{}, err
			}
		}
	}
	if coredb.IsNotFound(membershipErr) {
		memberID, err = identifiers.NewMemberID()
		if err != nil {
			return Summary{}, fmt.Errorf("generate invite member id: %w", err)
		}
		now := time.Now().UTC()
		role := string(domain.WorkspaceRoleMember)
		invitedDisplayName := displayNameFromEmail(user.Email)
		invitedPinyinFull, invitedPinyinInitials := pinyinsearch.Variants(invitedDisplayName)
		if _, err := tx.Exec(ctx, `
INSERT INTO members (id, workspace_id, type, display_name, display_name_pinyin, display_name_pinyin_initials, user_id, workspace_role, status, created_at, updated_at)
VALUES ($1, $2, 'human', $3, $4, $5, $6, $7, 'active', $8, $8)
`, memberID, workspaceID, invitedDisplayName, invitedPinyinFull, invitedPinyinInitials, user.ID, role, now); err != nil {
			return Summary{}, fmt.Errorf("insert invited member: %w", err)
		}
		workspaceRole = role
	} else if memberStatus != "active" {
		now := time.Now().UTC()
		if _, err := tx.Exec(ctx, `
UPDATE members
SET status = 'active', updated_at = $3
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human'
`, workspaceID, user.ID, now); err != nil {
			return Summary{}, fmt.Errorf("reactivate invited member: %w", err)
		}
	}
	if err := store.ensureAllChannelMembershipInTx(ctx, tx, workspaceID, memberID); err != nil {
		return Summary{}, fmt.Errorf("ensure #all for invited member: %w", err)
	}
	if err := store.ensureServiceAccountAudienceMembershipsInTx(ctx, tx, workspaceID, memberID); err != nil {
		return Summary{}, fmt.Errorf("ensure service account audience for invited member: %w", err)
	}
	if acceptedAt == nil {
		if _, err := tx.Exec(ctx, `
UPDATE workspace_invites
SET accepted_at = NOW()
WHERE workspace_id = $1 AND token_hash = $2
`, workspaceID, tokenHash); err != nil {
			return Summary{}, fmt.Errorf("mark invite accepted: %w", err)
		}
	}
	if err := tx.Commit(ctx); err != nil {
		return Summary{}, fmt.Errorf("commit accept invite: %w", err)
	}
	summary.MemberID = memberID
	summary.MemberUserID = user.ID
	summary.WorkspaceRole = workspaceRole
	return summary, nil
}

func workspaceInviteTargetMatchesUser(inviteEmail sql.NullString, inviteMobile sql.NullString, user model.User) bool {
	if inviteEmail.Valid && strings.TrimSpace(inviteEmail.String) != "" {
		if !user.HasVerifiedEmail() {
			return false
		}
		userEmail, err := authidentity.NormalizeStrictEmail(user.Email)
		if err != nil {
			userEmail, err = authidentity.NormalizeEmail(user.Email)
		}
		return err == nil && userEmail == strings.ToLower(strings.TrimSpace(inviteEmail.String))
	}
	if inviteMobile.Valid && strings.TrimSpace(inviteMobile.String) != "" {
		if !user.HasVerifiedMobile() {
			return false
		}
		userMobile, err := authidentity.NormalizeMobile(user.Mobile)
		if err != nil {
			return false
		}
		inviteMobileValue, err := authidentity.NormalizeMobile(inviteMobile.String)
		if err != nil {
			return false
		}
		return userMobile == inviteMobileValue
	}
	return false
}

func rollbackTx(tx coredb.Tx, ctx context.Context) {
	_ = tx.Rollback(ctx)
}

func hasActiveOrganizationSeat(ctx context.Context, queryer coredb.QueryRower, organizationID string, userID string) (bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM (
  SELECT om.user_id AS user_id
  FROM organization_members om
  WHERE om.organization_id = $1
    AND om.user_id = $2
    AND om.status = 'active'
  UNION
  SELECT m.user_id AS user_id
  FROM members m
  JOIN workspaces w ON w.id = m.workspace_id
  WHERE w.organization_id = $1
    AND m.user_id = $2
    AND m.type = 'human'
    AND m.status = 'active'
) seats
`, organizationID, userID)
	var count int64
	if err := row.Scan(&count); err != nil {
		return false, fmt.Errorf("check active organization seat: %w", err)
	}
	return count > 0, nil
}