package packs

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

	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/formulastorage"
)

// InspectLiveInstalledPackMissingPhysicalColumns reports physical columns that
// are registered in _System_Field (owned by packName) but absent from the
// actual INFORMATION_SCHEMA.COLUMNS.
//
// This oracle inspects live/installed state only; it does not consult the
// current source pack definition. Callers use the result to distinguish
// destructive dropped-column drift (registered metadata, missing physical
// column) from legitimate source upgrades (new source column has no
// _System_Field row yet — nothing is "missing" from the installed state).
//
// Returns the column identifiers sorted as "ObjectAPIName.FieldAPIName".
func InspectLiveInstalledPackMissingPhysicalColumns(ctx context.Context, db *sql.DB, packName string) ([]string, error) {
	fieldLocations, err := loadPackOwnedFieldLocations(ctx, db, packName)
	if err != nil {
		return nil, fmt.Errorf("inspect live installed pack %s field locations: %w", packName, err)
	}
	if len(fieldLocations) == 0 {
		return nil, nil
	}

	physicalColumns, err := schemaProvisioner.LoadPhysicalColumnShapes(db)
	if err != nil {
		return nil, fmt.Errorf("inspect live installed pack %s physical columns: %w", packName, err)
	}

	var missing []string
	for _, loc := range fieldLocations {
		tableKey := strings.ToLower(loc.objectAPIName)
		fieldKey := strings.ToLower(loc.fieldAPIName)
		if _, ok := physicalColumns[tableKey][fieldKey]; !ok {
			missing = append(missing, loc.objectAPIName+"."+loc.fieldAPIName)
		}
	}
	sort.Strings(missing)
	return missing, nil
}

// RepairPackOwnedMissingPhysicalColumns rebuilds missing physical columns for
// pack-owned _System_Field rows from live metadata. It iterates all non-deleted
// pack-owned (pack or pack_retained) field rows for packName, checks whether
// their physical columns exist in INFORMATION_SCHEMA, and rebuilds any missing
// ones using BuildManagedFieldColumnDefinition + ReconcileTableColumns.
//
// This is the metadata-driven repair path for #1392 production state: live
// _System_Field declares the field as pack-owned but the physical column is
// absent. The authoritative source for column definitions is the live metadata
// row, not the current pack spec — so this repairs fields even when the current
// source omits them (as long as the live row is still pack-owned and not
// deleted).
//
// Returns the list of repaired column identifiers ("ObjectAPIName.FieldAPIName").
func RepairPackOwnedMissingPhysicalColumns(ctx context.Context, db *sql.DB, packName string) ([]string, error) {
	if db == nil {
		return nil, fmt.Errorf("db is unavailable for pack-owned missing column repair")
	}

	fieldLocations, err := loadPackOwnedFieldLocations(ctx, db, packName)
	if err != nil {
		return nil, fmt.Errorf("load pack-owned field locations for %s: %w", packName, err)
	}
	if len(fieldLocations) == 0 {
		return nil, nil
	}

	physicalColumns, err := schemaProvisioner.LoadPhysicalColumnShapes(db)
	if err != nil {
		return nil, fmt.Errorf("load physical column shapes for pack-owned repair: %w", err)
	}

	var repaired []string
	for _, loc := range fieldLocations {
		tableKey := strings.ToLower(loc.objectAPIName)
		fieldKey := strings.ToLower(loc.fieldAPIName)
		if _, ok := physicalColumns[tableKey][fieldKey]; ok {
			continue // physical column exists
		}

		// Physical column is missing for a still-declared pack-owned field —
		// rebuild from live _System_Field metadata.
		colDef, err := persistence.BuildManagedFieldColumnDefinition(ctx, db, loc.objectAPIName, loc.fieldAPIName)
		if err != nil {
			continue // skip fields where metadata is insufficient
		}
		tableDef := domainschema.TableDefinition{
			TableName: loc.objectAPIName,
			Columns:   []domainschema.ColumnDefinition{colDef},
		}
		if err := schemaProvisioner.ReconcileTableColumns(db, []domainschema.TableDefinition{tableDef}); err != nil {
			continue // skip fields where reconcile fails
		}
		repaired = append(repaired, loc.objectAPIName+"."+loc.fieldAPIName)
	}
	sort.Strings(repaired)
	return repaired, nil
}

// loadPackOwnedFieldLocations returns the (objectAPIName, fieldAPIName) pairs
// for all non-deleted _System_Field rows owned by packName that require a
// physical column. Formula-backed fields are filtered through
// formulastorage.Resolve so active view-only formulas are excluded while active
// write_materialized formulas remain in the physical-column contract.
func loadPackOwnedFieldLocations(ctx context.Context, db *sql.DB, packName string) ([]struct{ objectAPIName, fieldAPIName string }, error) {
	query := fmt.Sprintf(
		"SELECT o.`%s`, f.`%s`, COALESCE(f.`%s`, ''), COALESCE(f.`%s`, ''), f.`%s`, COALESCE(f.`%s`, '')"+
			" FROM `%s` f JOIN `%s` o ON o.`%s` = f.`%s`"+
			" WHERE COALESCE(f.`%s`, '') IN (?, ?) AND COALESCE(f.`%s`, '') = ?"+
			" AND COALESCE(f.`%s`, 0) = 0 AND COALESCE(o.`%s`, 0) = 0",
		constants.FieldSysObject_APIName,
		constants.FieldSysField_APIName,
		constants.FieldSysField_FormulaMode,
		constants.FieldSysField_Formula,
		constants.FieldSysField_ProjectionConfig,
		constants.FieldSysField_FormulaStorageMode,
		constants.TableField,
		constants.TableObject,
		constants.FieldSysObject_ID,
		constants.FieldSysField_ObjectID,
		constants.FieldSysField_OwnerKind,
		constants.FieldSysField_OwnerPack,
		constants.FieldSysField_IsDeleted,
		constants.FieldSysObject_IsDeleted,
	)
	rows, err := db.QueryContext(ctx, query, constants.FieldOwnerKindPack, constants.FieldOwnerKindPackRetained, strings.TrimSpace(packName))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var locations []struct{ objectAPIName, fieldAPIName string }
	for rows.Next() {
		var objectAPIName, fieldAPIName, formulaMode, formula, formulaStorageMode string
		var projectionConfig sql.NullString
		if err := rows.Scan(&objectAPIName, &fieldAPIName, &formulaMode, &formula, &projectionConfig, &formulaStorageMode); err != nil {
			return nil, err
		}
		resolved, err := formulastorage.Resolve(formulastorage.Spec{
			FormulaMode:         formulaMode,
			FormulaStorageMode:  formulaStorageMode,
			Formula:             formula,
			HasProjectionConfig: projectionConfig.Valid && strings.TrimSpace(projectionConfig.String) != "",
		})
		if err != nil {
			return nil, err
		}
		if resolved.FormulaBacked && !resolved.RequiresPhysicalColumn() {
			continue
		}
		locations = append(locations, struct{ objectAPIName, fieldAPIName string }{
			objectAPIName: strings.TrimSpace(objectAPIName),
			fieldAPIName:  strings.TrimSpace(fieldAPIName),
		})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return locations, nil
}