package controlplane

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"reflect"
	"strings"

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
	"github.com/nexuscrm/kernel/pkg/schemautil"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

const migrationIdMultiPicklistWrapperRepair = "20260619_multipicklist_wrapper_repair"

// MigrateMultiPicklistWrapperRepair repairs the rare historical multipicklist
// double-wrapper array shape (for example ["[\"A\",\"B\"]"]) after the common
// semicolon storage backfill has converted legacy JSON STRING rows. The repair
// is field-aware: it only unwraps nested values when the field's declared
// Options prove every inner token is an authored picklist value.
func MigrateMultiPicklistWrapperRepair(ctx context.Context, db *sql.DB) error {
	if db == nil {
		return fmt.Errorf("MigrateMultiPicklistWrapperRepair requires a database")
	}
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdMultiPicklistWrapperRepair)
	if err != nil {
		return err
	}
	if applied {
		log.Println("   ⏭  MigrateMultiPicklistWrapperRepair: already applied; skipping")
		return nil
	}

	result, err := repairMultiPicklistWrapperStorage(ctx, db)
	if err != nil {
		return fmt.Errorf("MigrateMultiPicklistWrapperRepair: %w", err)
	}
	if result.Unresolved > 0 {
		return fmt.Errorf("MigrateMultiPicklistWrapperRepair: %d unresolved wrapper-looking row(s) remain; run field-metadata cleanup before stamping %s", result.Unresolved, migrationIdMultiPicklistWrapperRepair)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIdMultiPicklistWrapperRepair,
		1,
		"Repair field-aware multi-select picklist double-wrapper JSON array storage when declared options make it safe.",
	); err != nil {
		return err
	}

	if result.Updated > 0 {
		log.Printf("   🧼 MigrateMultiPicklistWrapperRepair: repaired %d row(s)", result.Updated)
	} else {
		log.Println("   ✅ MigrateMultiPicklistWrapperRepair: no rows needed repair")
	}
	return nil
}

type multiPicklistWrapperRepairResult struct {
	Updated    int
	Unresolved int
}

func repairMultiPicklistWrapperStorage(ctx context.Context, db *sql.DB) (multiPicklistWrapperRepairResult, error) {
	objMetaByID := make(map[string]*models.ObjectMetadata)
	objRows, err := db.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s` FROM `%s` WHERE COALESCE(`%s`,0) = 0",
			constants.ColSysObject_ID,
			constants.ColSysObject_APIName,
			constants.FieldSysObject_ReadSource,
			constants.TableObject,
			constants.FieldSysObject_IsDeleted,
		),
	)
	if err != nil {
		return multiPicklistWrapperRepairResult{}, fmt.Errorf("query _System_Object for read source resolution: %w", err)
	}
	for objRows.Next() {
		var id, apiName string
		var readSourceRaw sql.NullString
		if err := objRows.Scan(&id, &apiName, &readSourceRaw); err != nil {
			objRows.Close()
			return multiPicklistWrapperRepairResult{}, fmt.Errorf("scan _System_Object: %w", err)
		}
		var readSource *models.ObjectReadSourceConfig
		if readSourceRaw.Valid && strings.TrimSpace(readSourceRaw.String) != "" {
			var cfg models.ObjectReadSourceConfig
			if err := json.Unmarshal([]byte(readSourceRaw.String), &cfg); err != nil {
				objRows.Close()
				return multiPicklistWrapperRepairResult{}, fmt.Errorf("decode _System_Object ReadSource JSON for %s: %w", apiName, err)
			}
			readSource = &cfg
		}
		objMetaByID[id] = &models.ObjectMetadata{
			APIName:    strings.TrimSpace(apiName),
			ReadSource: readSource,
		}
	}
	objRows.Close()
	if err := objRows.Err(); err != nil {
		return multiPicklistWrapperRepairResult{}, fmt.Errorf("iterate _System_Object: %w", err)
	}

	type fieldCandidate struct {
		objID        string
		apiName      string
		physicalName string
		options      models.PicklistOptions
	}
	var candidates []fieldCandidate
	fieldRows, err := db.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT f.`%s`, f.`%s`, f.`%s`, f.`%s` FROM `%s` f "+
				"WHERE COALESCE(f.`%s`,0) = 0 "+
				"AND ( "+
				"  (LOWER(TRIM(f.`%s`)) IN ('multipicklist','multiselectpicklist') "+
				"   AND (f.`%s` IS NULL OR LOWER(TRIM(f.`%s`)) = '' "+
				"     OR (LOWER(TRIM(f.`%s`)) = 'active' AND LOWER(TRIM(COALESCE(f.`%s`,''))) = 'write_materialized')))"+
				"  OR "+
				"  (LOWER(TRIM(COALESCE(f.`%s`,''))) IN ('multipicklist','multiselectpicklist') "+
				"   AND LOWER(TRIM(COALESCE(f.`%s`,''))) = 'write_materialized')"+
				")",
			constants.ColSysField_ObjectID,
			constants.ColSysField_APIName,
			constants.ColSysField_PhysicalName,
			constants.ColSysField_Options,
			constants.TableField,
			constants.FieldSysField_IsDeleted,
			constants.FieldSysField_Type,
			constants.FieldSysField_FormulaMode,
			constants.FieldSysField_FormulaMode,
			constants.FieldSysField_FormulaMode,
			constants.FieldSysField_FormulaStorageMode,
			constants.FieldSysField_ReturnType,
			constants.FieldSysField_FormulaStorageMode,
		),
	)
	if err != nil {
		return multiPicklistWrapperRepairResult{}, fmt.Errorf("query multipicklist fields: %w", err)
	}
	for fieldRows.Next() {
		var objID, apiName string
		var physicalName, optionsRaw sql.NullString
		if err := fieldRows.Scan(&objID, &apiName, &physicalName, &optionsRaw); err != nil {
			fieldRows.Close()
			return multiPicklistWrapperRepairResult{}, fmt.Errorf("scan multipicklist field: %w", err)
		}
		options, err := decodePicklistOptions(optionsRaw)
		if err != nil {
			fieldRows.Close()
			return multiPicklistWrapperRepairResult{}, fmt.Errorf("decode Options JSON for field %s: %w", apiName, err)
		}
		candidates = append(candidates, fieldCandidate{
			objID:        objID,
			apiName:      strings.TrimSpace(apiName),
			physicalName: strings.TrimSpace(physicalName.String),
			options:      options,
		})
	}
	fieldRows.Close()
	if err := fieldRows.Err(); err != nil {
		return multiPicklistWrapperRepairResult{}, fmt.Errorf("iterate multipicklist fields: %w", err)
	}

	var total multiPicklistWrapperRepairResult
	for _, c := range candidates {
		obj, ok := objMetaByID[c.objID]
		if !ok {
			continue
		}
		tableName, err := obj.ResolveReadSourceTable()
		if err != nil {
			return total, fmt.Errorf("resolve read source table for %s: %w", c.apiName, err)
		}
		if !safeSQLIdentifierRE.MatchString(tableName) {
			return total, fmt.Errorf("unsafe physical table name %q", tableName)
		}
		colName := resolvePhysicalColumnName(c.apiName, c.physicalName)
		if !safeSQLIdentifierRE.MatchString(colName) {
			return total, fmt.Errorf("unsafe physical column name %q for field %s.%s", colName, c.apiName, c.physicalName)
		}
		field := &models.FieldMetadata{
			APIName: c.apiName,
			Options: c.options,
		}
		result, err := repairMultiPicklistWrapperColumn(ctx, db, tableName, colName, field)
		if err != nil {
			return total, fmt.Errorf("repair %s.%s: %w", tableName, colName, err)
		}
		total.Updated += result.Updated
		total.Unresolved += result.Unresolved
	}
	return total, nil
}

func decodePicklistOptions(raw sql.NullString) (models.PicklistOptions, error) {
	if !raw.Valid || strings.TrimSpace(raw.String) == "" {
		return nil, nil
	}
	var options models.PicklistOptions
	if err := json.Unmarshal([]byte(raw.String), &options); err != nil {
		return nil, err
	}
	return options, nil
}

func repairMultiPicklistWrapperColumn(ctx context.Context, db *sql.DB, tableName, columnName string, field *models.FieldMetadata) (multiPicklistWrapperRepairResult, error) {
	rows, err := db.QueryContext(ctx, multiPicklistWrapperCandidateSelectQuery(tableName, columnName))
	if err != nil {
		return multiPicklistWrapperRepairResult{}, fmt.Errorf("query wrapper values in %s.%s: %w", tableName, columnName, err)
	}

	var result multiPicklistWrapperRepairResult
	for rows.Next() {
		var id, value string
		if err := rows.Scan(&id, &value); err != nil {
			rows.Close()
			return multiPicklistWrapperRepairResult{}, fmt.Errorf("scan %s.%s: %w", tableName, columnName, err)
		}

		if !schemautil.HasUnresolvedMultiPicklistWrapperStorageForField(value, field) {
			continue
		}
		normalized := schemautil.NormalizeMultiPicklistStorageBackfillForField(value, field)
		normStr, ok := normalized.(string)
		if !ok {
			result.Unresolved++
			continue
		}
		if schemautil.HasUnresolvedMultiPicklistWrapperStorageForField(normStr, field) {
			result.Unresolved++
			continue
		}
		if jsonStorageSemanticallyEqual(value, normStr) {
			continue
		}
		execResult, err := db.ExecContext(ctx,
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = CAST(? AS JSON) WHERE `Id` = ? AND `%s` = CAST(? AS JSON)",
				tableName, columnName, columnName),
			normStr, id, value,
		)
		if err != nil {
			rows.Close()
			return result, fmt.Errorf("update %s.%s for Id=%s: %w", tableName, columnName, id, err)
		}
		affected, _ := execResult.RowsAffected()
		result.Updated += int(affected)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return result, fmt.Errorf("iterate %s.%s: %w", tableName, columnName, err)
	}
	return result, nil
}

func multiPicklistWrapperCandidateSelectQuery(tableName, columnName string) string {
	return fmt.Sprintf(
		"SELECT `Id`, `%s` FROM `%s` WHERE `%s` IS NOT NULL AND JSON_TYPE(`%s`) = 'ARRAY' AND JSON_SEARCH(`%s`, 'one', '[%%') IS NOT NULL",
		columnName,
		tableName,
		columnName,
		columnName,
		columnName,
	)
}

func jsonStorageSemanticallyEqual(left, right string) bool {
	var leftValue interface{}
	if err := json.Unmarshal([]byte(left), &leftValue); err != nil {
		return strings.TrimSpace(left) == strings.TrimSpace(right)
	}
	var rightValue interface{}
	if err := json.Unmarshal([]byte(right), &rightValue); err != nil {
		return strings.TrimSpace(left) == strings.TrimSpace(right)
	}
	return reflect.DeepEqual(leftValue, rightValue)
}