package packs

import (
	"context"
	"fmt"
	"strings"

	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/formulastorage"
)

type readSourceLiveFieldRow struct {
	fieldAPIName       string
	ownerKind          string
	ownerPack          string
	formulaMode        string
	formulaStorageMode string
	formula            string
	projectionConfig   string
}

func loadReadSourceLiveFields(ctx context.Context, exec persistence.Executor, objectAPIName string) ([]readSourceLiveFieldRow, error) {
	rows, err := exec.QueryContext(
		ctx,
		`SELECT f.ApiName,
		        COALESCE(f.OwnerKind, ''),
		        COALESCE(f.OwnerPack, ''),
		        COALESCE(f.FormulaMode, ''),
		        COALESCE(f.FormulaStorageMode, ''),
		        COALESCE(f.Formula, ''),
		        COALESCE(f.ProjectionConfig, '')
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE o.ApiName = ? AND COALESCE(f.IsDeleted, 0) = 0 AND COALESCE(o.IsDeleted, 0) = 0`,
		objectAPIName,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]readSourceLiveFieldRow, 0)
	for rows.Next() {
		var row readSourceLiveFieldRow
		if err := rows.Scan(
			&row.fieldAPIName,
			&row.ownerKind,
			&row.ownerPack,
			&row.formulaMode,
			&row.formulaStorageMode,
			&row.formula,
			&row.projectionConfig,
		); err != nil {
			return nil, err
		}
		row.fieldAPIName = strings.TrimSpace(row.fieldAPIName)
		row.ownerKind = strings.TrimSpace(strings.ToLower(row.ownerKind))
		row.ownerPack = strings.TrimSpace(row.ownerPack)
		row.formulaMode = strings.TrimSpace(strings.ToLower(row.formulaMode))
		row.formulaStorageMode = strings.TrimSpace(strings.ToLower(row.formulaStorageMode))
		row.formula = strings.TrimSpace(row.formula)
		row.projectionConfig = strings.TrimSpace(row.projectionConfig)
		out = append(out, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return out, nil
}

func (r readSourceLiveFieldRow) registryRow() packFieldRegistryRow {
	return packFieldRegistryRow{
		fieldAPIName: r.fieldAPIName,
		ownerKind:    r.ownerKind,
		ownerPack:    r.ownerPack,
	}
}

func classifyReadSourceLiveFields(rows []readSourceLiveFieldRow, expectedFields map[string]struct{}, ownerPack string) ([]packFieldRegistryRow, []readSourceLiveFieldRow, []readSourceLiveFieldRow) {
	stalePackFields := make([]packFieldRegistryRow, 0)
	unexpectedLiveFields := make([]readSourceLiveFieldRow, 0)
	activeExpectedComputedFields := make([]readSourceLiveFieldRow, 0)

	for _, row := range rows {
		fieldKey := strings.ToLower(strings.TrimSpace(row.fieldAPIName))
		if _, ok := expectedFields[fieldKey]; ok {
			resolved, err := formulastorage.Resolve(formulastorage.Spec{
				FormulaMode:         row.formulaMode,
				FormulaStorageMode:  row.formulaStorageMode,
				Formula:             row.formula,
				HasProjectionConfig: row.projectionConfig != "",
			})
			_ = err
			if resolved.RequiresFormulaView() {
				activeExpectedComputedFields = append(activeExpectedComputedFields, row)
			}
			continue
		}

		registryRow := row.registryRow()
		if registryRow.isOwnedByPack(ownerPack) || registryRow.isOwnedByRemovingPack(ownerPack) {
			stalePackFields = append(stalePackFields, registryRow)
			continue
		}
		unexpectedLiveFields = append(unexpectedLiveFields, row)
	}

	return stalePackFields, unexpectedLiveFields, activeExpectedComputedFields
}

func formatReadSourceFieldOwner(row readSourceLiveFieldRow) string {
	ownerKind := row.ownerKind
	if ownerKind == "" {
		ownerKind = "<blank>"
	}
	ownerPack := row.ownerPack
	if ownerPack == "" {
		ownerPack = "<blank>"
	}
	return fmt.Sprintf("%s/%s", ownerKind, ownerPack)
}