package packs

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

	"github.com/nexuscrm/shared/pkg/constants"
)

type packObjectCustomSettingRow struct {
	apiName       string
	customSetting sql.NullBool
	isCustomized  bool
	deleted       bool
}

// VerifyPackObjectCustomSetting fail-closes on drift between object-spec
// declarations and the persisted _System_Object.CustomSetting authority.
// This fact is behavior-affecting for runtime describe/read and repair
// selection, so object-row customization must not silently suppress it.
func VerifyPackObjectCustomSetting(db *sql.DB, ownerPack string, specs []ObjectSpec, manageability Manageability) error {
	if db == nil {
		return nil
	}
	_ = manageability

	rows, err := loadPackObjectCustomSettingRows(db)
	if err != nil {
		return err
	}

	var errs []string
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		row, ok := rows[strings.ToLower(apiName)]
		if !ok || row.deleted {
			continue
		}
		live := row.customSetting.Valid && row.customSetting.Bool
		want := spec.CustomSetting
		if live != want || !row.customSetting.Valid {
			errs = append(errs, fmt.Sprintf(
				"%s _System_Object row for %s CustomSetting = %s, want %t",
				ownerPack,
				apiName,
				formatPackBoolDefault(row.customSetting),
				want,
			))
		}
	}
	if len(errs) == 0 {
		return nil
	}
	return fmt.Errorf("%s", strings.Join(errs, "\n  - "))
}

// BackfillObjectMetadataCustomSettingFromActivePacks repairs warm-upgrade
// _System_Object rows that predate the canonical CustomSetting column by
// replaying active source-backed object-spec declarations into persisted
// object metadata before runtime readers or follow-on repairs consume it.
func BackfillObjectMetadataCustomSettingFromActivePacks(ctx context.Context, db *sql.DB) (int, error) {
	return backfillObjectMetadataCustomSettingFromActivePacks(ctx, db, nil)
}

// BackfillObjectMetadataCustomSettingFromActivePacksWithComponentKindRegistrationSet
// carries one distribution-owned registration snapshot through active-source resolution.
func BackfillObjectMetadataCustomSettingFromActivePacksWithComponentKindRegistrationSet(ctx context.Context, db *sql.DB, registrations ComponentKindRegistrationSet) (int, error) {
	return backfillObjectMetadataCustomSettingFromActivePacks(ctx, db, &registrations)
}

func backfillObjectMetadataCustomSettingFromActivePacks(ctx context.Context, db *sql.DB, registrations *ComponentKindRegistrationSet) (int, error) {
	if db == nil {
		return 0, nil
	}
	rows, err := db.QueryContext(ctx, `
SELECT Name, Category, ManifestFormatVersion, SourceKind, SourceURI
FROM _System_InstalledPack
WHERE Category = ?
  AND Status = 'active'
  AND COALESCE(IsDeleted, 0) = 0
ORDER BY Name`, string(AppPack))
	if err != nil {
		return 0, fmt.Errorf("query active source-backed packs for CustomSetting backfill: %w", err)
	}
	defer rows.Close()

	total := 0
	for rows.Next() {
		var name, category string
		var manifestFormatVersion sql.NullString
		var sourceKind, sourceURI sql.NullString
		if err := rows.Scan(&name, &category, &manifestFormatVersion, &sourceKind, &sourceURI); err != nil {
			return total, fmt.Errorf("scan active source-backed pack for CustomSetting backfill: %w", err)
		}
		name = strings.TrimSpace(name)
		if name == "" {
			continue
		}
		resolvedSourceKind, resolvedSourceURI, err := legacySourceBackedBackfillPackSourceIdentity(name, sourceKind.String, sourceURI.String)
		if err != nil {
			return total, fmt.Errorf("resolve source identity for source-backed pack %s CustomSetting backfill: %w", name, err)
		}
		pack, err := resolveActiveInstalledSourceBackedPackWithStoredManifestVersionAndComponentKindRegistrationSet(
			ctx,
			db,
			name,
			Category(strings.TrimSpace(category)),
			resolvedSourceKind,
			resolvedSourceURI,
			manifestFormatVersion.String,
			registrations,
		)
		if err != nil {
			return total, fmt.Errorf("resolve active source-backed pack %s for CustomSetting backfill: %w", name, err)
		}
		updated, err := backfillObjectMetadataCustomSettingFromPack(ctx, db, pack)
		if err != nil {
			return total, err
		}
		total += updated
	}
	if err := rows.Err(); err != nil {
		return total, fmt.Errorf("iterate active source-backed packs for CustomSetting backfill: %w", err)
	}
	return total, nil
}

func backfillObjectMetadataCustomSettingFromPack(ctx context.Context, db *sql.DB, pack *SourceBackedPack) (int, error) {
	if db == nil || pack == nil {
		return 0, nil
	}
	total := 0
	for _, component := range pack.Components() {
		if component.Kind() != ComponentKindObjectSpec {
			continue
		}
		projection, err := component.ExpectedProjection()
		if err != nil {
			return total, fmt.Errorf("%s object_spec %s projection for CustomSetting backfill: %w", pack.Name(), component.BusinessKey(), err)
		}
		specs, ok := projection.([]ObjectSpec)
		if !ok {
			return total, fmt.Errorf("%s object_spec %s projection for CustomSetting backfill has type %T, want []ObjectSpec", pack.Name(), component.BusinessKey(), projection)
		}
		updated, err := backfillObjectMetadataCustomSettingForSpecs(ctx, db, specs)
		if err != nil {
			return total, err
		}
		total += updated
	}
	return total, nil
}

func backfillObjectMetadataCustomSettingForSpecs(ctx context.Context, db *sql.DB, specs []ObjectSpec) (int, error) {
	if db == nil {
		return 0, nil
	}
	total := 0
	for _, spec := range specs {
		apiName := strings.TrimSpace(spec.APIName)
		if apiName == "" {
			continue
		}
		want := 0
		if spec.CustomSetting {
			want = 1
		}
		res, err := db.ExecContext(
			ctx,
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = ?, `%s` = NOW() WHERE LOWER(`%s`) = LOWER(?) AND COALESCE(`%s`, 0) = 0 AND (`%s` IS NULL OR COALESCE(`%s`, 0) <> ?)",
				constants.TableObject,
				constants.FieldSysObject_CustomSetting,
				constants.FieldLastModifiedDate,
				constants.FieldSysObject_APIName,
				constants.FieldSysObject_IsDeleted,
				constants.FieldSysObject_CustomSetting,
				constants.FieldSysObject_CustomSetting,
			),
			want,
			apiName,
			want,
		)
		if err != nil {
			return total, fmt.Errorf("backfill CustomSetting for %s: %w", apiName, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			total += int(n)
		}
	}
	return total, nil
}

func loadPackObjectCustomSettingRows(db *sql.DB) (map[string]packObjectCustomSettingRow, error) {
	rows, err := db.Query(fmt.Sprintf(
		"SELECT `%s`, `%s`, COALESCE(`%s`, 0), COALESCE(`%s`, 0) FROM `%s`",
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_CustomSetting,
		constants.FieldSysObject_IsCustomized,
		constants.FieldSysObject_IsDeleted,
		constants.TableObject,
	))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make(map[string]packObjectCustomSettingRow)
	for rows.Next() {
		var row packObjectCustomSettingRow
		var isCustomized, deleted int
		if err := rows.Scan(&row.apiName, &row.customSetting, &isCustomized, &deleted); err != nil {
			return nil, err
		}
		row.isCustomized = isCustomized != 0
		row.deleted = deleted != 0
		out[strings.ToLower(strings.TrimSpace(row.apiName))] = row
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func formatPackBoolDefault(v sql.NullBool) string {
	if !v.Valid {
		return "NULL"
	}
	if v.Bool {
		return "1"
	}
	return "0"
}