package channel

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

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

type CommandTagExecer interface {
	Exec(context.Context, string, ...any) (coredb.Result, error)
}

func InsertChannelMembershipIfMissing(ctx context.Context, execer CommandTagExecer, channelID string, memberID string, createdAt time.Time) error {
	err := execInsertIgnoringDuplicateMembership(
		ctx,
		execer,
		"channel_membership_insert",
		`
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
  unread_generation = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN unread_generation + 1
    ELSE unread_generation
  END,
  authority_from_at = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN VALUES(authority_from_at)
    ELSE COALESCE(authority_from_at, created_at)
  END,
  authority_to_at = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN NULL
    ELSE authority_to_at
  END,
  removed_at = NULL,
  archived_at = NULL,
  effective_to_seq = NULL,
  updated_at = VALUES(updated_at)
`,
		strings.TrimSpace(channelID),
		strings.TrimSpace(memberID),
		createdAt.UTC(),
	)
	if err != nil {
		return fmt.Errorf("insert channel membership: %w", err)
	}
	return nil
}

func InsertAllChannelMembershipIfMissing(ctx context.Context, execer CommandTagExecer, workspaceID string, memberID string, createdAt time.Time) error {
	err := execInsertIgnoringDuplicateMembership(
		ctx,
		execer,
		"channel_membership_all_insert",
		`
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
)
SELECT s.id, $2, 'public', NULL, 0, 0, 0, 0, 1, NULL, LEAST($3, CURRENT_TIMESTAMP(6)), NULL, $3, $3, NULL, NULL
FROM scopes s
JOIN scope_channel_profiles p
  ON p.scope_id = s.id
 AND p.workspace_id = s.workspace_id
WHERE s.workspace_id = $1
  AND s.type = 'channel'
  AND s.status = 'active'
  AND s.archived_at IS NULL
  AND p.slug = 'all'
ON DUPLICATE KEY UPDATE
  unread_generation = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN unread_generation + 1
    ELSE unread_generation
  END,
  authority_from_at = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN VALUES(authority_from_at)
    ELSE COALESCE(authority_from_at, created_at)
  END,
  authority_to_at = CASE
    WHEN removed_at IS NOT NULL OR archived_at IS NOT NULL OR effective_to_seq IS NOT NULL THEN NULL
    ELSE authority_to_at
  END,
  removed_at = NULL,
  archived_at = NULL,
  effective_to_seq = NULL,
  updated_at = VALUES(updated_at)
`,
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(memberID),
		createdAt.UTC(),
	)
	if err != nil {
		return fmt.Errorf("insert #all channel membership: %w", err)
	}
	return nil
}

func execInsertIgnoringDuplicateMembership(ctx context.Context, execer CommandTagExecer, savepointName string, query string, args ...any) error {
	if tx, ok := execer.(coredb.Tx); ok {
		if err := ExecWithinSavepoint(ctx, tx, savepointName, query, args...); err == nil {
			return nil
		} else if UniqueViolationMatches(err, "scope_memberships_pkey", "PRIMARY") {
			return nil
		} else {
			return err
		}
	}

	if _, err := execer.Exec(ctx, query, args...); err != nil {
		if UniqueViolationMatches(err, "scope_memberships_pkey", "PRIMARY") {
			return nil
		}
		return err
	}
	return nil
}

func ExecWithinSavepoint(ctx context.Context, tx coredb.Tx, savepointName string, query string, args ...any) error {
	if _, err := tx.Exec(ctx, "SAVEPOINT "+savepointName); err != nil {
		return fmt.Errorf("create savepoint %s: %w", savepointName, err)
	}

	if _, err := tx.Exec(ctx, query, args...); err != nil {
		if rollbackErr := rollbackSavepoint(ctx, tx, savepointName); rollbackErr != nil {
			return fmt.Errorf("rollback savepoint %s after insert error: %w", savepointName, rollbackErr)
		}
		if releaseErr := releaseSavepoint(ctx, tx, savepointName); releaseErr != nil {
			return fmt.Errorf("release savepoint %s after insert error: %w", savepointName, releaseErr)
		}
		return err
	}

	if releaseErr := releaseSavepoint(ctx, tx, savepointName); releaseErr != nil {
		return fmt.Errorf("release savepoint %s: %w", savepointName, releaseErr)
	}
	return nil
}

func rollbackSavepoint(ctx context.Context, tx coredb.Tx, savepointName string) error {
	_, err := tx.Exec(ctx, "ROLLBACK TO SAVEPOINT "+savepointName)
	return err
}

func releaseSavepoint(ctx context.Context, tx coredb.Tx, savepointName string) error {
	_, err := tx.Exec(ctx, "RELEASE SAVEPOINT "+savepointName)
	return err
}

func UniqueViolationMatches(err error, constraints ...string) bool {
	constraint, ok := coredb.UniqueViolationConstraint(err)
	if !ok {
		return false
	}
	for _, expected := range constraints {
		if constraint == expected || strings.HasSuffix(constraint, "."+expected) {
			return true
		}
	}
	return false
}