package connector_webhooks

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

	connector_packstate "github.com/pingcap-inc/nexuscrm-connectors/features/connector/packstate"
)

// activePackInfoByCategory queries _System_InstalledPack for the active install
// of packName in the given category, returning (PackInstallId, PackVersion, error).
// Shared by AuthorizeCrossPackProfileDependency; previously duplicated as
// activePackInfoByCategory (pkg/serverruntime/connector_push_runtime.go) and
// directoryActivePackInfoByCategory (application/connectors/directory_reader_adapter.go).
func activePackInfoByCategory(ctx context.Context, db *sql.DB, packName, category string) (string, string, error) {
	var installID string
	var version sql.NullString
	err := db.QueryRowContext(ctx, `
SELECT COALESCE(PackInstallId, ''), PackVersion
  FROM _System_InstalledPack
 WHERE Name = ?
   AND Category = ?
   AND Status = 'active'
   AND COALESCE(IsDeleted, 0) = 0
 LIMIT 1`, packName, category).Scan(&installID, &version)
	if err == sql.ErrNoRows {
		return "", "", fmt.Errorf("connector profile dependency: pack %q (%s) is not active", packName, category)
	}
	if err != nil {
		return "", "", fmt.Errorf("connector profile dependency: query active pack %q (%s): %w", packName, category, err)
	}
	installID = strings.TrimSpace(installID)
	if installID == "" {
		return "", "", fmt.Errorf("connector profile dependency: pack %q (%s) has blank PackInstallId", packName, category)
	}
	if version.Valid {
		return installID, strings.TrimSpace(version.String), nil
	}
	return installID, "", nil
}

// AuthorizeCrossPackProfileDependency enforces pack authority for cross-pack
// connector-profile access (push and directory-pull).
//
// Same-pack (case-insensitive) passes immediately — no DB required.
// Cross-pack is allowed only when an active companion-consent dependency row
// authorizes the requester's app-pack to use the profile owner's connector-pack.
//
// This is the single shared implementation; previously duplicated as
// authorizeConnectorPushProfileDependency (pkg/serverruntime/connector_push_runtime.go)
// and authorizeDirectoryProfileDependency (application/connectors/directory_reader_adapter.go).
func AuthorizeCrossPackProfileDependency(ctx context.Context, db *sql.DB, requesterOwnerPack, profileOwnerPack string) error {
	requesterOwnerPack = strings.TrimSpace(requesterOwnerPack)
	profileOwnerPack = strings.TrimSpace(profileOwnerPack)
	if requesterOwnerPack == "" {
		return fmt.Errorf("connector profile dependency: requester owner pack is required")
	}
	if profileOwnerPack == "" {
		return fmt.Errorf("connector profile dependency: profile owner pack is required")
	}
	if strings.EqualFold(requesterOwnerPack, profileOwnerPack) {
		return nil
	}
	if db == nil {
		return fmt.Errorf("connector profile dependency: db is required for cross-pack profile %s -> %s", requesterOwnerPack, profileOwnerPack)
	}
	requesterInstallID, _, err := activePackInfoByCategory(ctx, db, requesterOwnerPack, string(connector_packstate.AppPack))
	if err != nil {
		return err
	}
	_, providerVersion, err := activePackInfoByCategory(ctx, db, profileOwnerPack, string(connector_packstate.ConnectorPack))
	if err != nil {
		return err
	}
	if err := CheckCompanionConsent(ctx, db, requesterOwnerPack, requesterInstallID, profileOwnerPack, providerVersion); err != nil {
		return fmt.Errorf("connector profile dependency: %w", err)
	}
	return nil
}

// CompanionFlowIncarnation describes the companion app-pack incarnation that
// owns the flow DeveloperName targeted by a cross-pack connector-inbound route.
type CompanionFlowIncarnation struct {
	// CompanionPackName is the Name of the companion app-pack (_System_InstalledPack.Name).
	CompanionPackName string
	// CompanionPackInstallID is the companion's PackInstallId at resolution time.
	CompanionPackInstallID string
	// CompanionFlowVersionID is the FlowDefinition.ActiveVersionId — the exact
	// Flow.Id the worker must execute via getFlowVersionById.
	CompanionFlowVersionID string
}

// ResolveCompanionFlowIncarnation resolves the companion app-pack incarnation
// for a given flowDeveloperName at enqueue time.
//
// It returns (incr, true, nil) when the flow's owning pack is a different pack
// from the routing connector (cross-pack case).
// It returns (zero, false, nil) when the flow is owned by the same pack as the
// routing connector (same-pack / legacy path — no companion snapshot needed).
// It returns an error (which callers map to ErrCompanionConsentMissing) when the
// FlowDefinition is missing, the owning pack is not active, or the
// ActiveVersionId is blank.
//
// db is a live *sql.DB handle.
// namespace is the routing connector-pack name (lower-cased, as canonicalized by
// the dispatcher).
// flowDeveloperName is the flow name resolved by ResolveFlow.
func ResolveCompanionFlowIncarnation(ctx context.Context, db *sql.DB, namespace, flowDeveloperName string) (CompanionFlowIncarnation, bool, error) {
	flowDeveloperName = strings.TrimSpace(flowDeveloperName)
	namespace = strings.TrimSpace(namespace)
	if flowDeveloperName == "" {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: empty flowDeveloperName")
	}

	// Step 1: look up FlowDefinition to find OwnerPack, ActiveVersionId, and
	// whether the flow is tenant-customized.
	var ownerPack, activeVersionID string
	var isCustomized bool
	err := db.QueryRowContext(ctx, `
SELECT COALESCE(OwnerPack, ''), COALESCE(ActiveVersionId, ''), COALESCE(IsCustomized, 0)
  FROM FlowDefinition
 WHERE DeveloperName = ?
 LIMIT 1`, flowDeveloperName).Scan(&ownerPack, &activeVersionID, &isCustomized)
	if err == sql.ErrNoRows {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: FlowDefinition %q not found", flowDeveloperName)
	}
	if err != nil {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: query FlowDefinition %q: %w", flowDeveloperName, err)
	}
	ownerPack = strings.TrimSpace(ownerPack)
	activeVersionID = strings.TrimSpace(activeVersionID)

	// Step 2: only a NON-customized flow owned by a DIFFERENT app pack is a
	// cross-pack companion flow. Everything else stays on the existing
	// same-pack/legacy path with NO companion snapshot (return false):
	//   - ownerPack == ""       → ownerless / admin-authored / legacy flow.
	//   - EqualFold(owner, ns)  → same-pack flow (companion IS the connector).
	//   - isCustomized          → a tenant-customized flow is not a protected
	//     pack-owned standard flow, so the runtime never applies pack-ownership
	//     enforcement to it (executor_helpers.go getActiveFlowVersion /
	//     authorizeProtectedStandardFlow gate on !DefinitionIsCustomized).
	// Forcing any of these into the cross-pack consent path would 503 routes that
	// work today (the route component defers same-pack checks to the protected-flow
	// path and does not require pack ownership at all).
	if ownerPack == "" || strings.EqualFold(ownerPack, namespace) || isCustomized {
		return CompanionFlowIncarnation{}, false, nil
	}

	// Cross-pack: the incarnation snapshot needs a concrete active flow version.
	if activeVersionID == "" {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: FlowDefinition %q has no ActiveVersionId", flowDeveloperName)
	}

	// Step 3: cross-pack — resolve the companion app-pack incarnation.
	var packInstallID string
	err = db.QueryRowContext(ctx, `
SELECT COALESCE(PackInstallId, '')
  FROM _System_InstalledPack
 WHERE Name = ?
   AND Category = ?
   AND Status = ?
   AND COALESCE(IsDeleted, 0) = 0
 LIMIT 1`, ownerPack, string(connector_packstate.AppPack), "active").Scan(&packInstallID)
	if err == sql.ErrNoRows {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: companion app-pack %q not active", ownerPack)
	}
	if err != nil {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: query companion app-pack %q: %w", ownerPack, err)
	}
	packInstallID = strings.TrimSpace(packInstallID)
	if packInstallID == "" {
		return CompanionFlowIncarnation{}, false, fmt.Errorf("resolve companion flow incarnation: companion app-pack %q has blank PackInstallId", ownerPack)
	}

	return CompanionFlowIncarnation{
		CompanionPackName:      ownerPack,
		CompanionPackInstallID: packInstallID,
		CompanionFlowVersionID: activeVersionID,
	}, true, nil
}

// CheckCompanionConsent verifies that the companion app-pack has declared a
// PackDependency on the provider connector-pack (namespace) AND that the
// dependency row still matches the current companion incarnation AND that the
// provider's version satisfies the constraint (if any).
//
// Returns nil on success.
// Returns an error whose message starts with "consent missing" or "version
// unsatisfied" so callers can map to the appropriate sentinel.
//
// This must only be called at enqueue time. The drain path never reads
// _System_InstalledPackDependency.
func CheckCompanionConsent(ctx context.Context, db *sql.DB, companionPackName, companionPackInstallID, namespace, providerPackVersion string) error {
	var depInstallID, versionConstraint string
	var versionConstraintNull sql.NullString
	err := db.QueryRowContext(ctx, `
SELECT COALESCE(DependentPackInstallId, ''), VersionConstraint
  FROM _System_InstalledPackDependency
 WHERE DependentPackName = ?
   AND DependsOnPackName = ?
   AND COALESCE(IsDeleted, 0) = 0
 LIMIT 1`, companionPackName, namespace).Scan(&depInstallID, &versionConstraintNull)
	if err == sql.ErrNoRows {
		return fmt.Errorf("consent missing: companion %q has no active dependency declaration on connector %q", companionPackName, namespace)
	}
	if err != nil {
		return fmt.Errorf("consent missing: query dependency row for companion %q on connector %q: %w", companionPackName, namespace, err)
	}
	depInstallID = strings.TrimSpace(depInstallID)
	if versionConstraintNull.Valid {
		versionConstraint = strings.TrimSpace(versionConstraintNull.String)
	}

	// Incarnation drift: the dependency row was written for a different install of
	// the companion than the currently active one.
	if depInstallID != companionPackInstallID {
		return fmt.Errorf("consent missing: companion %q dependency row was written for install %q but current active incarnation is %q (incarnation drift)", companionPackName, depInstallID, companionPackInstallID)
	}

	// Version constraint check.
	if versionConstraint != "" && !connector_packstate.VersionSatisfiesConstraint(providerPackVersion, versionConstraint) {
		return fmt.Errorf("version unsatisfied: companion %q requires connector %q version %q but provider version is %q", companionPackName, namespace, versionConstraint, providerPackVersion)
	}

	return nil
}