package app

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

	"github.com/tidbcloud/AgentTeam/internal/core/authidentity"
	"github.com/tidbcloud/AgentTeam/internal/core/secrets"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/workspace"
)

func (store *Store) HasPendingWorkspaceInviteForEmail(ctx context.Context, email string) (bool, error) {
	row := store.db.QueryRow(ctx, `
SELECT EXISTS (
  SELECT 1
  FROM workspace_invites
  WHERE email = $1
    AND accepted_at IS NULL
    AND revoked_at IS NULL
    AND expires_at > NOW()
)
`, email)
	var found bool
	if err := row.Scan(&found); err != nil {
		return false, fmt.Errorf("check pending workspace invite: %w", err)
	}
	return found, nil
}

func (store *Store) ListPendingWorkspaceInvitesForUser(ctx context.Context, user model.User) ([]workspace.PendingWorkspaceInvite, error) {
	targets := workspaceInviteTargetsForUser(user)
	if targets.Email == "" && targets.Mobile == "" {
		return []workspace.PendingWorkspaceInvite{}, nil
	}

	rows, err := store.db.Query(ctx, `
SELECT id,
       workspace_id,
       workspace_name,
       inviter_name,
       email,
       mobile,
       created_at,
       expires_at
FROM (
  SELECT i.id,
         i.workspace_id,
         w.name AS workspace_name,
         COALESCE(NULLIF(inviter.display_name, ''), inviter.name) AS inviter_name,
         i.email,
         i.mobile,
         i.created_at,
         i.expires_at
  FROM workspace_invites i
  JOIN workspaces w ON w.id = i.workspace_id
  JOIN users inviter ON inviter.id = i.created_by_user_id
  LEFT JOIN workspace_invite_dismissals d ON d.invite_id = i.id AND d.user_id = $3
  WHERE $1 <> ''
    AND i.email = $1
    AND i.accepted_at IS NULL
    AND i.revoked_at IS NULL
    AND i.expires_at > NOW()
    AND d.invite_id IS NULL
  UNION ALL
  SELECT i.id,
         i.workspace_id,
         w.name AS workspace_name,
         COALESCE(NULLIF(inviter.display_name, ''), inviter.name) AS inviter_name,
         i.email,
         i.mobile,
         i.created_at,
         i.expires_at
  FROM workspace_invites i
  JOIN workspaces w ON w.id = i.workspace_id
  JOIN users inviter ON inviter.id = i.created_by_user_id
  LEFT JOIN workspace_invite_dismissals d ON d.invite_id = i.id AND d.user_id = $3
  WHERE $2 <> ''
    AND i.mobile = $2
    AND i.accepted_at IS NULL
    AND i.revoked_at IS NULL
    AND i.expires_at > NOW()
    AND d.invite_id IS NULL
) pending
ORDER BY created_at DESC, id ASC
`, targets.Email, targets.Mobile, user.ID)
	if err != nil {
		return nil, fmt.Errorf("list pending workspace invites for user: %w", err)
	}
	defer rows.Close()

	invites := make([]workspace.PendingWorkspaceInvite, 0)
	for rows.Next() {
		var invite workspace.PendingWorkspaceInvite
		var invitedEmail sql.NullString
		var invitedMobile sql.NullString
		if err := rows.Scan(
			&invite.ID,
			&invite.WorkspaceID,
			&invite.WorkspaceName,
			&invite.InviterName,
			&invitedEmail,
			&invitedMobile,
			&invite.CreatedAt,
			&invite.ExpiresAt,
		); err != nil {
			return nil, fmt.Errorf("scan pending workspace invite: %w", err)
		}
		if invitedEmail.Valid {
			invite.InvitedEmail = invitedEmail.String
		}
		if invitedMobile.Valid {
			invite.InvitedMobileHint = authidentity.RedactMobile(invitedMobile.String)
		}
		invites = append(invites, invite)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate pending workspace invites: %w", err)
	}
	return invites, nil
}

func (store *Store) DismissPendingWorkspaceInviteForUser(ctx context.Context, user model.User, inviteID string) (bool, error) {
	inviteID = strings.TrimSpace(inviteID)
	if inviteID == "" {
		return false, nil
	}
	targets := workspaceInviteTargetsForUser(user)
	if targets.Email == "" && targets.Mobile == "" {
		return false, forbidden("Email or mobile must be verified to dismiss this invite")
	}

	commandTag, err := store.db.Exec(ctx, `
INSERT INTO workspace_invite_dismissals (invite_id, user_id, dismissed_at)
SELECT i.id, $2, NOW()
FROM workspace_invites i
WHERE i.id = $1
  AND (($3 <> '' AND i.email = $3) OR ($4 <> '' AND i.mobile = $4))
  AND i.accepted_at IS NULL
  AND i.revoked_at IS NULL
  AND i.expires_at > NOW()
ON DUPLICATE KEY UPDATE dismissed_at = VALUES(dismissed_at)
`, inviteID, user.ID, targets.Email, targets.Mobile)
	if err != nil {
		return false, fmt.Errorf("dismiss pending workspace invite: %w", err)
	}
	if commandTag.RowsAffected() > 0 {
		return true, nil
	}

	row := store.db.QueryRow(ctx, `
SELECT EXISTS (
  SELECT 1
  FROM workspace_invite_dismissals d
  JOIN workspace_invites i ON i.id = d.invite_id
  WHERE d.invite_id = $1
    AND d.user_id = $2
    AND (($3 <> '' AND i.email = $3) OR ($4 <> '' AND i.mobile = $4))
    AND i.accepted_at IS NULL
    AND i.revoked_at IS NULL
    AND i.expires_at > NOW()
)
`, inviteID, user.ID, targets.Email, targets.Mobile)
	var dismissed bool
	if err := row.Scan(&dismissed); err != nil {
		return false, fmt.Errorf("check pending workspace invite dismissal: %w", err)
	}
	return dismissed, nil
}

func (store *Store) FindWorkspaceInviteInfoByToken(ctx context.Context, token string) (workspace.WorkspaceInviteInfo, bool, error) {
	tokenHash := secrets.HashToken(token)
	row := store.db.QueryRow(ctx, `
SELECT i.workspace_id, w.name,
       COALESCE(NULLIF(inviter.display_name, ''), inviter.name),
       i.email,
       i.mobile,
       i.expires_at, i.accepted_at, i.revoked_at
FROM workspace_invites i
JOIN workspaces w ON w.id = i.workspace_id
JOIN users inviter ON inviter.id = i.created_by_user_id
WHERE i.token_hash = $1
`, tokenHash)

	var info workspace.WorkspaceInviteInfo
	var invitedEmail sql.NullString
	var invitedMobile sql.NullString
	var expiresAt time.Time
	var acceptedAt *time.Time
	var revokedAt *time.Time
	if err := row.Scan(&info.WorkspaceID, &info.WorkspaceName, &info.InviterName, &invitedEmail, &invitedMobile, &expiresAt, &acceptedAt, &revokedAt); err != nil {
		if coredb.IsNotFound(err) {
			return workspace.WorkspaceInviteInfo{}, false, nil
		}
		return workspace.WorkspaceInviteInfo{}, false, fmt.Errorf("load invite info: %w", err)
	}
	if invitedEmail.Valid {
		info.InvitedEmail = invitedEmail.String
	}
	if invitedMobile.Valid {
		info.InvitedMobileHint = authidentity.RedactMobile(invitedMobile.String)
	}
	if (!invitedEmail.Valid || strings.TrimSpace(invitedEmail.String) == "") &&
		(!invitedMobile.Valid || strings.TrimSpace(invitedMobile.String) == "") {
		return workspace.WorkspaceInviteInfo{}, false, nil
	}
	if revokedAt != nil {
		return workspace.WorkspaceInviteInfo{}, true, badRequest("invite revoked")
	}
	if time.Now().UTC().After(expiresAt) {
		return workspace.WorkspaceInviteInfo{}, true, badRequest("invite expired")
	}
	return info, true, nil
}

func (store *Store) FindWorkspaceInviteInfoByID(ctx context.Context, inviteID string) (workspace.WorkspaceInviteInfo, bool, error) {
	inviteID = strings.TrimSpace(inviteID)
	if inviteID == "" {
		return workspace.WorkspaceInviteInfo{}, false, nil
	}
	row := store.db.QueryRow(ctx, `
SELECT i.workspace_id, w.name,
       COALESCE(NULLIF(inviter.display_name, ''), inviter.name),
       i.email,
       i.mobile,
       i.expires_at, i.accepted_at, i.revoked_at
FROM workspace_invites i
JOIN workspaces w ON w.id = i.workspace_id
JOIN users inviter ON inviter.id = i.created_by_user_id
WHERE i.id = $1
`, inviteID)

	var info workspace.WorkspaceInviteInfo
	var invitedEmail sql.NullString
	var mobile sql.NullString
	var expiresAt time.Time
	var acceptedAt *time.Time
	var revokedAt *time.Time
	if err := row.Scan(&info.WorkspaceID, &info.WorkspaceName, &info.InviterName, &invitedEmail, &mobile, &expiresAt, &acceptedAt, &revokedAt); err != nil {
		if coredb.IsNotFound(err) {
			return workspace.WorkspaceInviteInfo{}, false, nil
		}
		return workspace.WorkspaceInviteInfo{}, false, fmt.Errorf("load invite info: %w", err)
	}
	if invitedEmail.Valid {
		info.InvitedEmail = invitedEmail.String
	}
	if (!invitedEmail.Valid || strings.TrimSpace(invitedEmail.String) == "") &&
		(!mobile.Valid || strings.TrimSpace(mobile.String) == "") {
		return workspace.WorkspaceInviteInfo{}, false, nil
	}
	if acceptedAt != nil {
		return workspace.WorkspaceInviteInfo{}, true, badRequest("invite already accepted")
	}
	if revokedAt != nil {
		return workspace.WorkspaceInviteInfo{}, true, badRequest("invite revoked")
	}
	if time.Now().UTC().After(expiresAt) {
		return workspace.WorkspaceInviteInfo{}, true, badRequest("invite expired")
	}
	if mobile.Valid {
		info.InvitedMobileHint = authidentity.RedactMobile(mobile.String)
	}
	return info, true, nil
}

func (store *Store) GetWorkspaceInviteInfoByToken(ctx context.Context, token string) (workspace.WorkspaceInviteInfo, error) {
	info, found, err := store.FindWorkspaceInviteInfoByToken(ctx, token)
	if err != nil {
		return workspace.WorkspaceInviteInfo{}, err
	}
	if !found {
		return workspace.WorkspaceInviteInfo{}, badRequest("invalid invite token")
	}
	return info, nil
}

func (store *Store) AcceptWorkspaceInviteByToken(ctx context.Context, user model.User, token string) (workspace.Summary, error) {
	summary, found, err := store.AcceptWorkspaceInviteByTokenIfExists(ctx, user, token)
	if err != nil {
		return workspace.Summary{}, err
	}
	if !found {
		return workspace.Summary{}, badRequest("invalid invite token")
	}
	return summary, nil
}

func (store *Store) AcceptWorkspaceInviteByTokenIfExists(ctx context.Context, user model.User, token string) (workspace.Summary, bool, error) {
	info, found, err := store.FindWorkspaceInviteInfoByToken(ctx, token)
	if err != nil {
		return workspace.Summary{}, found, err
	}
	if !found {
		return workspace.Summary{}, false, nil
	}
	summary, err := store.AcceptWorkspaceInvite(ctx, user, info.WorkspaceID, token)
	if err != nil {
		return workspace.Summary{}, true, err
	}
	return summary, true, nil
}

func (store *Store) ListWorkspaceInvitesForUser(ctx context.Context, userID string, workspaceID string) ([]workspace.WorkspaceInvite, error) {
	page, err := store.ListWorkspaceInvitesForUserPage(ctx, userID, workspaceID, workspace.WorkspaceInviteListOptions{Limit: workspace.MaxWorkspaceInviteListLimit})
	if err != nil {
		return nil, err
	}
	return page.Invites, nil
}

func (store *Store) ListWorkspaceInvitesForUserPage(ctx context.Context, userID string, workspaceID string, options workspace.WorkspaceInviteListOptions) (workspace.WorkspaceInvitePage, error) {
	if err := store.requireWorkspaceMemberManagement(ctx, userID, workspaceID); err != nil {
		return workspace.WorkspaceInvitePage{}, err
	}
	limit := normalizeWorkspaceInviteListLimit(options.Limit)
	offset, err := decodeWorkspaceInviteListCursor(options.Cursor)
	if err != nil {
		return workspace.WorkspaceInvitePage{}, err
	}

	rows, err := store.db.Query(ctx, `
SELECT id, email, mobile, created_at
FROM workspace_invites
WHERE workspace_id = $1
  AND revoked_at IS NULL
  AND accepted_at IS NULL
  AND expires_at > NOW()
ORDER BY created_at DESC, id ASC
LIMIT $2 OFFSET $3
`, workspaceID, limit+1, offset)
	if err != nil {
		return workspace.WorkspaceInvitePage{}, fmt.Errorf("list workspace invites: %w", err)
	}
	defer rows.Close()

	invites := make([]workspace.WorkspaceInvite, 0)
	for rows.Next() {
		var invite workspace.WorkspaceInvite
		var invitedEmail sql.NullString
		var invitedMobile sql.NullString
		if err := rows.Scan(&invite.ID, &invitedEmail, &invitedMobile, &invite.CreatedAt); err != nil {
			return workspace.WorkspaceInvitePage{}, fmt.Errorf("scan workspace invite: %w", err)
		}
		if invitedEmail.Valid {
			invite.InvitedEmail = invitedEmail.String
		}
		if invitedMobile.Valid {
			invite.InvitedMobileHint = authidentity.RedactMobile(invitedMobile.String)
		}
		invites = append(invites, invite)
	}
	if err := rows.Err(); err != nil {
		return workspace.WorkspaceInvitePage{}, fmt.Errorf("iterate workspace invites: %w", err)
	}
	hasMore := len(invites) > limit
	if hasMore {
		invites = invites[:limit]
	}
	nextCursor := ""
	if hasMore {
		nextCursor = strconv.Itoa(offset + limit)
	}
	return workspace.WorkspaceInvitePage{Invites: invites, HasMore: hasMore, NextCursor: nextCursor}, nil
}

func normalizeWorkspaceInviteListLimit(limit int) int {
	if limit <= 0 {
		return workspace.DefaultWorkspaceInviteListLimit
	}
	if limit > workspace.MaxWorkspaceInviteListLimit {
		return workspace.MaxWorkspaceInviteListLimit
	}
	return limit
}

func decodeWorkspaceInviteListCursor(raw string) (int, error) {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return 0, nil
	}
	offset, err := strconv.Atoi(trimmed)
	if err != nil || offset < 0 {
		return 0, fmt.Errorf("cursor must be a non-negative integer")
	}
	return offset, nil
}

func (store *Store) RevokeWorkspaceInviteForUser(ctx context.Context, userID string, workspaceID string, inviteID string) error {
	if err := store.requireWorkspaceMemberManagement(ctx, userID, workspaceID); err != nil {
		return err
	}
	commandTag, err := store.db.Exec(ctx, `
UPDATE workspace_invites
SET revoked_at = NOW()
WHERE workspace_id = $1 AND id = $2 AND revoked_at IS NULL
`, workspaceID, inviteID)
	if err != nil {
		return fmt.Errorf("revoke workspace invite: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}