package packs

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
)

// newConnectorCapabilityPackSweeperComponent is the synthetic pack-level
// component that guarantees stale _System_ConnectorCapabilityDeclaration rows
// owned by this pack get soft-deleted on reapply when the new manifest no
// longer declares them. Without this, dropping a (namespace, kind) from a
// connector pack manifest would leave the old declaration row live forever
// and runtime dispatch would keep authorizing it.
//
// Pattern mirrors newRecordTypePicklistValuePackSweeperComponent (record_type_picklist_value.go
// lines 387-404). That file documents the Codex-round-2 BLOCK on PR #919 that
// established this pattern.
//
// Synthetic component (NOT exposed via SourceBackedPack.Components or
// DeclaredComponents) — no payload hash contribution, no manifest business
// key, folded into InstallPlan/Verify only via lifecycleClosureComponents().
func newConnectorCapabilityPackSweeperComponent(packName string, declared []ConnectorCapabilityComponentPayload) PackComponent {
	manifestComponent := ManifestComponent{
		Kind:          ComponentKindConnectorCapability,
		BusinessKey:   "_pack_sweep",
		Manageability: ManageabilityLocked,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: manifestComponent,
		applyGroupKey:     string(ComponentKindConnectorCapability),
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applyConnectorCapabilityPackSweep(ctx, deps.DB, packName, declared)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifyConnectorCapabilityPackSweeper(ctx, deps.DB, packName, declared)
		},
	}
}

// applyConnectorCapabilityPackSweep soft-deletes any
// _System_ConnectorCapabilityDeclaration rows owned by packName whose
// (namespace, kind) is not in the declared set. When declared is empty, every
// owner-pack row is soft-deleted.
func applyConnectorCapabilityPackSweep(ctx context.Context, db *sql.DB, packName string, declared []ConnectorCapabilityComponentPayload) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}
	declaredSet := connectorCapabilityDeclaredSet(declared)

	rows, err := db.QueryContext(ctx, `
SELECT Id, Namespace, Kind
  FROM _System_ConnectorCapabilityDeclaration
 WHERE InstalledPackName = ? AND COALESCE(IsDeleted, 0) = 0`, packName)
	if err != nil {
		return fmt.Errorf("query connector capability declarations for sweep apply (pack %s): %w", packName, err)
	}
	defer rows.Close()

	var dropIDs []string
	for rows.Next() {
		var id, namespace, kind string
		if err := rows.Scan(&id, &namespace, &kind); err != nil {
			return fmt.Errorf("scan connector capability row for sweep apply: %w", err)
		}
		key := connectorCapabilityTupleKey(namespace, kind)
		if _, ok := declaredSet[key]; ok {
			continue
		}
		dropIDs = append(dropIDs, id)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate connector capability rows for sweep apply: %w", err)
	}

	if len(dropIDs) == 0 {
		return nil
	}
	sort.Strings(dropIDs)
	placeholders := strings.Repeat("?,", len(dropIDs))
	placeholders = strings.TrimRight(placeholders, ",")
	args := make([]interface{}, 0, 1+len(dropIDs))
	args = append(args, packName)
	for _, id := range dropIDs {
		args = append(args, id)
	}
	query := `UPDATE _System_ConnectorCapabilityDeclaration
   SET IsDeleted = 1, LastModifiedDate = CURRENT_TIMESTAMP
 WHERE InstalledPackName = ?
   AND COALESCE(IsDeleted, 0) = 0
   AND Id IN (` + placeholders + `)`
	if _, err := db.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("soft-delete stale connector capability declarations for pack %s: %w", packName, err)
	}
	return nil
}

// verifyConnectorCapabilityPackSweeper enforces the pack-level invariant that
// no _System_ConnectorCapabilityDeclaration row exists with InstalledPackName=packName
// whose (Namespace, Kind) is undeclared by the current manifest. It is the
// verify-side complement to the apply-side sweep so post-apply verification
// fails closed if the sweep was somehow skipped.
func verifyConnectorCapabilityPackSweeper(ctx context.Context, db *sql.DB, packName string, declared []ConnectorCapabilityComponentPayload) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}
	declaredSet := connectorCapabilityDeclaredSet(declared)

	rows, err := db.QueryContext(ctx, `
SELECT Namespace, Kind
  FROM _System_ConnectorCapabilityDeclaration
 WHERE InstalledPackName = ? AND COALESCE(IsDeleted, 0) = 0`, packName)
	if err != nil {
		return fmt.Errorf("query connector capability declarations for sweep verify (pack %s): %w", packName, err)
	}
	defer rows.Close()

	var leftover []string
	for rows.Next() {
		var namespace, kind string
		if err := rows.Scan(&namespace, &kind); err != nil {
			return fmt.Errorf("scan connector capability row for sweep verify: %w", err)
		}
		key := connectorCapabilityTupleKey(namespace, kind)
		if _, ok := declaredSet[key]; ok {
			continue
		}
		leftover = append(leftover, namespace+":"+kind)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate connector capability rows for sweep verify: %w", err)
	}

	if len(leftover) > 0 {
		sort.Strings(leftover)
		return fmt.Errorf("pack %s has %d undeclared connector capability declaration(s) still live: %s",
			packName, len(leftover), strings.Join(leftover, ", "))
	}
	return nil
}

func connectorCapabilityDeclaredSet(declared []ConnectorCapabilityComponentPayload) map[string]struct{} {
	set := make(map[string]struct{}, len(declared))
	for _, p := range declared {
		set[connectorCapabilityTupleKey(p.Namespace, p.Kind)] = struct{}{}
	}
	return set
}

func connectorCapabilityTupleKey(namespace, kind string) string {
	return strings.ToLower(strings.TrimSpace(namespace)) + "\x00" + strings.ToLower(strings.TrimSpace(kind))
}