package controlplane

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

	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/persistence"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

func installedPackHasSourceIdentity(row installedPackRow) bool {
	return strings.TrimSpace(row.SourceKind.String) != "" && strings.TrimSpace(row.SourceURI.String) != ""
}

func sourceBackedPackFromInstalledRow(ctx context.Context, db *sql.DB, row installedPackRow, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	return sourceBackedPackFromInstalledRowAtSourceRevision(ctx, db, row, row.SourceRevision.String, opts...)
}

func sourceBackedPackFromInstalledRowAtSourceRevision(ctx context.Context, db *sql.DB, row installedPackRow, sourceRevision string, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	pack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptions(
		row.Name,
		packs.Category(strings.TrimSpace(row.Category)),
		row.ManifestFormatVersion.String,
		row.SourceKind.String,
		row.SourceURI.String,
		sourceRevision,
		packSourceOptionsWithSchema(ctx, db),
		opts...,
	)
	if err != nil {
		return nil, fmt.Errorf("installed pack %s: %w", row.Name, err)
	}
	return pack, nil
}

func sourceBackedPackFromInstalledRowWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, row installedPackRow, registrations packs.ComponentKindRegistrationSet, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	return sourceBackedPackFromInstalledRowAtSourceRevisionWithComponentKindRegistrationSet(ctx, db, row, row.SourceRevision.String, registrations, opts...)
}

func sourceBackedPackFromInstalledRowAtSourceRevisionWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, row installedPackRow, sourceRevision string, registrations packs.ComponentKindRegistrationSet, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	pack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptionsAndComponentKindRegistrationSet(
		row.Name, packs.Category(strings.TrimSpace(row.Category)), row.ManifestFormatVersion.String,
		row.SourceKind.String, row.SourceURI.String, sourceRevision,
		packSourceOptionsWithSchema(ctx, db), registrations, opts...,
	)
	if err != nil {
		return nil, fmt.Errorf("installed pack %s: %w", row.Name, err)
	}
	return pack, nil
}

func sourceBackedPackFromInstalledRowWithFormulaBundles(ctx context.Context, db *sql.DB, row installedPackRow, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	return sourceBackedPackFromInstalledRowAtSourceRevisionWithFormulaBundles(ctx, db, row, row.SourceRevision.String, formulaFunctionBundlesForValidation, hasFormulaFunctionBundlesForValidation, opts...)
}

func sourceBackedPackFromInstalledRowAtSourceRevisionWithFormulaBundles(ctx context.Context, db *sql.DB, row installedPackRow, sourceRevision string, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	pack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptionsAndFormulaBundles(
		row.Name,
		packs.Category(strings.TrimSpace(row.Category)),
		row.ManifestFormatVersion.String,
		row.SourceKind.String,
		row.SourceURI.String,
		sourceRevision,
		packSourceOptionsWithSchema(ctx, db),
		formulaFunctionBundlesForValidation,
		hasFormulaFunctionBundlesForValidation,
		opts...,
	)
	if err != nil {
		return nil, fmt.Errorf("installed pack %s: %w", row.Name, err)
	}
	return pack, nil
}

func sourceBackedPackFromInstalledRowWithFormulaBundlesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, row installedPackRow, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, registrations packs.ComponentKindRegistrationSet, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	return sourceBackedPackFromInstalledRowAtSourceRevisionWithFormulaBundlesAndComponentKindRegistrationSet(ctx, db, row, row.SourceRevision.String, formulaFunctionBundlesForValidation, hasFormulaFunctionBundlesForValidation, registrations, opts...)
}

func sourceBackedPackFromInstalledRowAtSourceRevisionWithFormulaBundlesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, row installedPackRow, sourceRevision string, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, registrations packs.ComponentKindRegistrationSet, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	return sourceBackedPackFromInstalledRowAtSourceRevisionWithPackSourceOptionsAndFormulaBundlesAndComponentKindRegistrationSet(
		ctx, db, row, sourceRevision, packSourceOptionsWithSchema(ctx, db), formulaFunctionBundlesForValidation,
		hasFormulaFunctionBundlesForValidation, registrations, opts...,
	)
}

func sourceBackedPackFromInstalledRowAtSourceRevisionWithPackSourceOptionsAndFormulaBundlesAndComponentKindRegistrationSet(ctx context.Context, db *sql.DB, row installedPackRow, sourceRevision string, sourceOptions packs.PackSourceOptions, formulaFunctionBundlesForValidation []formula.FunctionBundle, hasFormulaFunctionBundlesForValidation bool, registrations packs.ComponentKindRegistrationSet, opts ...packs.SourceBackedPackOption) (*packs.SourceBackedPack, error) {
	sourceOptions.ObjectSchemaResolver = installedPackObjectSchemaResolver(ctx, db)
	pack, err := packs.ResolveInstalledSourceBackedPackForInstalledRowAtSourceRevisionWithPackSourceOptionsAndFormulaBundlesAndComponentKindRegistrationSet(
		row.Name, packs.Category(strings.TrimSpace(row.Category)), row.ManifestFormatVersion.String,
		row.SourceKind.String, row.SourceURI.String, sourceRevision, sourceOptions,
		formulaFunctionBundlesForValidation, hasFormulaFunctionBundlesForValidation, registrations, opts...,
	)
	if err != nil {
		return nil, fmt.Errorf("installed pack %s: %w", row.Name, err)
	}
	return pack, nil
}

func sourceIdentityMatches(row installedPackRow, meta packs.PackHashMetadata) bool {
	if !row.SourceKind.Valid || !row.SourceURI.Valid {
		return false
	}
	rowKind, rowURI, rowErr := canonicalPackSourceIdentity(row.SourceKind.String, row.SourceURI.String)
	metaKind, metaURI, metaErr := canonicalPackSourceIdentity(meta.SourceKind, meta.SourceURI)
	if rowErr != nil || metaErr != nil || rowKind != metaKind || rowURI != metaURI {
		return false
	}
	// local_path identity is the resolved path. Revision is mutable provenance
	// for the current tree at that path, not a distinct source identity.
	if rowKind == string(packs.PackSourceKindLocalPath) {
		return true
	}
	if row.SourceRevision.Valid != meta.SourceRevisionValid {
		return false
	}
	if row.SourceRevision.Valid && row.SourceRevision.String != meta.SourceRevision {
		return false
	}
	return true
}

// canonicalPackSourceIdentity delegates identity parsing to the pack-source
// contract. It therefore recognizes only the supported in_repo legacy alias,
// which ParsePackSourceRef maps to official_repo, rather than maintaining a
// second alias table in lifecycle orchestration.
func canonicalPackSourceIdentity(kind, uri string) (string, string, error) {
	kind = strings.TrimSpace(kind)
	if strings.Contains(kind, ":") {
		return "", "", fmt.Errorf("pack source kind %q must be one exact token", kind)
	}
	ref, err := packs.ParsePackSourceRef(kind + ":" + strings.TrimSpace(uri))
	if err != nil {
		return "", "", err
	}
	return strings.TrimSpace(ref.Kind), strings.TrimSpace(ref.URI), nil
}

func getInstalledPackRowByName(ctx context.Context, db *sql.DB, name string) (installedPackRow, bool, error) {
	row, ok, err := queryInstalledPackRowByName(ctx, db, name, false, true, false)
	if err != nil && persistence.IsUnknownColumnError(err) {
		return queryInstalledPackRowByName(ctx, db, name, false, false, false)
	}
	return row, ok, err
}

func getInstalledPackRowByNameForRetryAllowance(ctx context.Context, db *sql.DB, name string) (installedPackRow, bool, error) {
	row, ok, err := queryInstalledPackRowByName(ctx, db, name, true, true, false)
	if err != nil && persistence.IsUnknownColumnError(err) {
		return queryInstalledPackRowByName(ctx, db, name, true, false, false)
	}
	return row, ok, err
}

// getInstalledPackRowByNameForSourceRevisionAdvance is the sole retry-aware
// projection that also freezes the row and installation-incarnation identity.
// Its final fallback supports a stale pre-migration read; the post-repair
// execution consumer must require non-empty identity before any mutation.
func getInstalledPackRowByNameForSourceRevisionAdvance(ctx context.Context, db *sql.DB, name string) (installedPackRow, bool, error) {
	row, ok, err := queryInstalledPackRowByName(ctx, db, name, true, true, true)
	if err != nil && persistence.IsUnknownColumnError(err) {
		row, ok, err = queryInstalledPackRowByName(ctx, db, name, true, false, true)
	}
	if err != nil && persistence.IsUnknownColumnError(err) {
		return queryInstalledPackRowByName(ctx, db, name, true, false, false)
	}
	return row, ok, err
}

func queryInstalledPackRowByName(ctx context.Context, db *sql.DB, name string, allowRetryRows bool, includeHostRequirementsDigest bool, includeIncarnationIdentity bool) (installedPackRow, bool, error) {
	var row installedPackRow
	columns := []string{"Name"}
	if includeIncarnationIdentity {
		columns = []string{"Id", "Name", "COALESCE(PackInstallId, '')"}
	}
	columns = append(columns, "Category", "Status")
	if allowRetryRows {
		columns = append(columns, "COALESCE(IsDeleted, 0)")
	}
	columns = append(columns,
		"PackVersion",
		"ManifestFormatVersion",
		"PlatformCompatibility",
		"DesiredPayloadHash",
		"AppliedPayloadHash",
		"PayloadHashAlgorithm",
		"SourceKind",
		"SourceURI",
		"SourceRevision",
	)
	if includeHostRequirementsDigest {
		columns = append(columns, "HostRequirementsDigest")
	}
	query := `SELECT ` + strings.Join(columns, ", ") + `
			   FROM _System_InstalledPack
			  WHERE `
	if allowRetryRows {
		query += `Name = ?`
	} else {
		query += `COALESCE(IsDeleted, 0) = 0 AND Name = ?`
	}
	query += `
			  LIMIT 1`
	dest := []any{&row.Name}
	if includeIncarnationIdentity {
		dest = []any{&row.ID, &row.Name, &row.PackInstallID}
	}
	dest = append(dest, &row.Category, &row.Status)
	if allowRetryRows {
		dest = append(dest, &row.IsDeleted)
	}
	dest = append(dest,
		&row.PackVersion,
		&row.ManifestFormatVersion,
		&row.PlatformCompatibility,
		&row.DesiredPayloadHash,
		&row.AppliedPayloadHash,
		&row.PayloadHashAlgorithm,
		&row.SourceKind,
		&row.SourceURI,
		&row.SourceRevision,
	)
	if includeHostRequirementsDigest {
		dest = append(dest, &row.HostRequirementsDigest)
	}
	err := db.QueryRowContext(ctx, query, name).Scan(dest...)
	if err == sql.ErrNoRows {
		return installedPackRow{}, false, nil
	}
	if err != nil {
		if allowRetryRows {
			return installedPackRow{}, false, fmt.Errorf("query installed pack %s retry allowance: %w", name, err)
		}
		return installedPackRow{}, false, fmt.Errorf("query installed pack %s: %w", name, err)
	}
	if allowRetryRows && row.IsDeleted != 0 && row.Status != installedPackStatusFailed && row.Status != installedPackStatusInstalling {
		return installedPackRow{}, false, nil
	}
	return row, true, nil
}