package packs

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

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

const migrationIdBackfillChildCloneRulesCustomized = "20260518_child_clone_rules_customized"

// BackfillChildCloneRulesCustomized stamps legacy live ChildCloneRules values
// as L2-owned only when pack-side ownership is already known and the live value
// differs from that pack-owned value. Pack-owned omitted rules are canonicalized
// as [] instead of treated as unresolved. Rows without pack ownership identity
// stay unresolved so source-backed pack reconciliation can hydrate ownership
// first instead of treating IsCustom as proof of tenant authorship.
func BackfillChildCloneRulesCustomized(ctx context.Context, db *sql.DB) (int, error) {
	if db == nil {
		return 0, fmt.Errorf("BackfillChildCloneRulesCustomized requires a database")
	}
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdBackfillChildCloneRulesCustomized)
	if err != nil {
		return 0, err
	}
	if applied {
		return 0, nil
	}

	updated, err := ClassifyLegacyChildCloneRulesCustomized(ctx, db)
	if err != nil {
		return updated, err
	}
	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIdBackfillChildCloneRulesCustomized,
		1,
		"Classify legacy _System_Object.ChildCloneRules rows under the ChildCloneRulesCustomized ownership bit.",
	); err != nil {
		return updated, err
	}
	if updated > 0 {
		log.Printf("   🧼 BackfillChildCloneRulesCustomized: stamped %d row(s)", updated)
	}
	return updated, nil
}

// ClassifyLegacyChildCloneRulesCustomized is intentionally idempotent and not
// stamp-guarded. It is a migration/repair classifier for rows whose
// ChildCloneRulesPackValue is already hydrated; steady-state source-backed pack
// apply must not call it between sidecar persistence and object-spec upsert.
func ClassifyLegacyChildCloneRulesCustomized(ctx context.Context, db *sql.DB) (int, error) {
	rows, err := legacyChildCloneRulesRows(ctx, db)
	if err != nil {
		return 0, err
	}
	if len(rows) == 0 {
		return 0, nil
	}

	ids := make([]string, 0, len(rows))
	for _, row := range rows {
		mark, err := legacyChildCloneRulesRowNeedsCustomizedBit(row)
		if err != nil {
			return 0, err
		}
		if mark {
			ids = append(ids, row.id)
		}
	}
	if len(ids) == 0 {
		return 0, nil
	}

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("BackfillChildCloneRulesCustomized begin transaction: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = NOW() WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0",
		constants.TableObject,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysObject_ChildCloneRulesCustomized,
	)
	updated := 0
	for _, id := range ids {
		res, err := tx.ExecContext(ctx, updateSQL, id)
		if err != nil {
			return updated, fmt.Errorf("BackfillChildCloneRulesCustomized update %s: %w", id, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			updated += int(n)
		}
	}
	if err := tx.Commit(); err != nil {
		return updated, fmt.Errorf("BackfillChildCloneRulesCustomized commit: %w", err)
	}
	committed = true
	return updated, nil
}

type legacyChildCloneRulesRow struct {
	id                string
	apiName           string
	isCustom          bool
	liveRules         sql.NullString
	packName          string
	packBusinessKey   string
	packManageability string
	packRules         sql.NullString
}

func legacyChildCloneRulesRows(ctx context.Context, db *sql.DB) ([]legacyChildCloneRulesRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, `%s`, COALESCE(`%s`, 0), `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, ''), `%s` FROM `%s` "+
			"WHERE COALESCE(`%s`, 0) = 0 AND `%s` IS NOT NULL AND COALESCE(`%s`, 0) = 0 ORDER BY `%s`",
		constants.FieldID,
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_IsCustom,
		constants.FieldSysObject_ChildCloneRules,
		constants.FieldSysObject_ChildCloneRulesPackName,
		constants.FieldSysObject_ChildCloneRulesPackBusinessKey,
		constants.FieldSysObject_ChildCloneRulesPackManageability,
		constants.FieldSysObject_ChildCloneRulesPackValue,
		constants.TableObject,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_ChildCloneRules,
		constants.FieldSysObject_ChildCloneRulesCustomized,
		constants.FieldID,
	)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query legacy ChildCloneRules rows: %w", err)
	}
	defer rows.Close()

	var out []legacyChildCloneRulesRow
	for rows.Next() {
		var row legacyChildCloneRulesRow
		var isCustom int
		if err := rows.Scan(&row.id, &row.apiName, &isCustom, &row.liveRules, &row.packName, &row.packBusinessKey, &row.packManageability, &row.packRules); err != nil {
			return nil, fmt.Errorf("scan legacy ChildCloneRules row: %w", err)
		}
		row.id = strings.TrimSpace(row.id)
		row.apiName = strings.TrimSpace(row.apiName)
		row.packName = strings.TrimSpace(row.packName)
		row.packBusinessKey = strings.TrimSpace(row.packBusinessKey)
		row.packManageability = strings.TrimSpace(row.packManageability)
		row.isCustom = isCustom != 0
		if row.id != "" {
			out = append(out, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate legacy ChildCloneRules rows: %w", err)
	}
	return out, nil
}

func legacyChildCloneRulesRowNeedsCustomizedBit(row legacyChildCloneRulesRow) (bool, error) {
	if !row.liveRules.Valid || strings.TrimSpace(row.liveRules.String) == "" {
		return false, nil
	}
	if row.packName == "" || row.packBusinessKey == "" {
		return false, nil
	}
	if row.packManageability != "defaults_only" {
		return false, nil
	}
	live, err := canonicalLegacyChildCloneRulesJSON(row.liveRules)
	if err != nil {
		return false, fmt.Errorf("canonicalize live ChildCloneRules for %s: %w", row.apiName, err)
	}
	pack, err := canonicalLegacyChildCloneRulesJSON(row.packRules)
	if err != nil {
		return false, fmt.Errorf("canonicalize pack ChildCloneRules for %s: %w", row.apiName, err)
	}
	return !bytes.Equal(live, pack), nil
}

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

func canonicalLegacyChildCloneRules(rules []models.ChildCloneRule) ([]byte, error) {
	if len(rules) == 0 {
		return []byte("[]"), nil
	}
	type normalizedRule struct {
		ParentLookupField string            `json:"parentLookupField"`
		SourceObject      string            `json:"sourceObject"`
		SourceLookupField string            `json:"sourceLookupField"`
		SourceIdField     string            `json:"sourceIdField,omitempty"`
		HydrateSource     bool              `json:"hydrateSource,omitempty"`
		TargetObject      string            `json:"targetObject"`
		TargetLookupField string            `json:"targetLookupField"`
		FieldMappings     map[string]string `json:"fieldMappings"`
	}
	normalized := make([]normalizedRule, 0, len(rules))
	for _, rule := range rules {
		fieldMappings := map[string]string{}
		for key, value := range rule.FieldMappings {
			fieldMappings[key] = value
		}
		normalized = append(normalized, normalizedRule{
			ParentLookupField: rule.ParentLookupField,
			SourceObject:      rule.SourceObject,
			SourceLookupField: rule.SourceLookupField,
			SourceIdField:     rule.SourceIdField,
			HydrateSource:     rule.HydrateSource,
			TargetObject:      rule.TargetObject,
			TargetLookupField: rule.TargetLookupField,
			FieldMappings:     fieldMappings,
		})
	}
	sort.Slice(normalized, func(i, j int) bool {
		left, _ := json.Marshal(normalized[i])
		right, _ := json.Marshal(normalized[j])
		return bytes.Compare(left, right) < 0
	})
	return json.Marshal(normalized)
}