package packs

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

	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-controlplane/layoutownership"
)

// BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacks rewrites
// historical same-pack default layout IDs back to the canonical
// curated_<Object>_default identity before steady-state source-backed reconcile
// runs. This is bounded upgrade cleanup, not part of apply/verify identity.
func BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacks(ctx context.Context, db *sql.DB) (int, error) {
	return BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacksWithOptions(ctx, db, ActivePackBackfillOptions{})
}

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

// BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacksWithOptions
// defers packs already planned for component apply and resolves every other
// active declaration from its installed source.
func BackfillLegacySourceBackedCuratedDefaultLayoutIDsFromActivePacksWithOptions(ctx context.Context, db *sql.DB, options ActivePackBackfillOptions) (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 page layout ID backfill: %w", err)
	}
	defer rows.Close()

	var total int
	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 page layout ID backfill: %w", err)
		}
		name = strings.TrimSpace(name)
		if name == "" {
			continue
		}
		if options.defers(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 page layout ID backfill: %w", name, err)
		}
		pack, err := resolveActiveInstalledSourceBackedPackWithStoredManifestVersionAndComponentKindRegistrationSet(ctx, db, name, Category(strings.TrimSpace(category)), resolvedSourceKind, resolvedSourceURI, manifestFormatVersion.String, &options.ComponentKindRegistrationSet)
		if err != nil {
			return total, fmt.Errorf("resolve active source-backed pack %s for page layout ID backfill: %w", name, err)
		}
		updated, err := backfillLegacySourceBackedCuratedDefaultLayoutIDsFromPack(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 page layout ID backfill: %w", err)
	}
	return total, nil
}

func legacySourceBackedBackfillPackSourceIdentity(name, sourceKind, sourceURI string) (string, string, error) {
	name = strings.TrimSpace(name)
	sourceKind = strings.TrimSpace(sourceKind)
	sourceURI = strings.TrimSpace(sourceURI)
	if name == "" {
		return "", "", fmt.Errorf("missing installed pack name")
	}
	switch {
	case sourceKind == "" && sourceURI == "":
		return string(PackSourceKindOfficialRepo), name, nil
	case sourceKind == string(PackSourceKindOfficialRepo) && sourceURI == "":
		return string(PackSourceKindOfficialRepo), name, nil
	case sourceKind == "" && sourceURI == name:
		return string(PackSourceKindOfficialRepo), name, nil
	default:
		return sourceKind, sourceURI, nil
	}
}

func backfillLegacySourceBackedCuratedDefaultLayoutIDsFromPack(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() != ComponentKindPageLayout {
			continue
		}
		payload, err := component.ExpectedPayload()
		if err != nil {
			return total, fmt.Errorf("load %s page_layout payload %s: %w", pack.Name(), component.BusinessKey(), err)
		}
		layout, err := sourceBackedPageLayoutFromPayload(payload, component.BusinessKey())
		if err != nil {
			return total, fmt.Errorf("parse %s page_layout payload %s: %w", pack.Name(), component.BusinessKey(), err)
		}
		updated, err := backfillLegacySourceBackedCuratedDefaultLayoutID(ctx, db, pack.Name(), layout)
		if err != nil {
			return total, err
		}
		total += updated
	}
	return total, nil
}

func backfillLegacySourceBackedCuratedDefaultLayoutID(ctx context.Context, db *sql.DB, ownerPack string, expected models.PageLayout) (int, error) {
	if db == nil {
		return 0, nil
	}
	ownerPack = strings.TrimSpace(ownerPack)
	expectedID := strings.TrimSpace(expected.ID)
	if ownerPack == "" || !expected.IsDefault || expectedID == "" {
		return 0, nil
	}
	if expectedID != sourceBackedLayoutID(strings.TrimSpace(expected.ObjectAPIName), strings.TrimSpace(expected.LayoutName), true) {
		return 0, nil
	}

	row, err := loadLegacySourceBackedLayoutRewriteCandidate(ctx, db, expected.ObjectAPIName, expected.LayoutName, ownerPack, expectedID)
	if err != nil {
		return 0, err
	}
	if row == nil {
		return 0, nil
	}

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("begin source-backed page_layout ID backfill tx for %s/%s: %w", expected.ObjectAPIName, expected.LayoutName, err)
	}
	fkChecksRestored := false
	defer func() {
		if !fkChecksRestored {
			_, _ = tx.ExecContext(context.Background(), `SET FOREIGN_KEY_CHECKS = 1`)
		}
		_ = tx.Rollback()
	}()
	if _, err := tx.ExecContext(ctx, `SET FOREIGN_KEY_CHECKS = 0`); err != nil {
		return 0, fmt.Errorf("disable foreign key checks for source-backed page_layout backfill %s/%s: %w", expected.ObjectAPIName, expected.LayoutName, err)
	}

	canonicalExists, err := sourceBackedPageLayoutIDExistsTx(ctx, tx, expectedID)
	if err != nil {
		return 0, fmt.Errorf("query canonical source-backed page_layout %s: %w", expectedID, err)
	}
	if canonicalExists {
		return 0, fmt.Errorf("canonical source-backed page_layout %s already exists while legacy row %s is still live", expectedID, row.ID)
	}

	configJSON, err := canonicalizePageLayoutConfigID(row.ConfigJSON, expectedID, expected.ObjectAPIName)
	if err != nil {
		return 0, fmt.Errorf("canonicalize source-backed page_layout %s config: %w", row.ID, err)
	}
	if err := rewriteLegacySourceBackedPageLayoutIDTx(ctx, tx, row, expectedID, expected.ObjectAPIName, expected.LayoutName, configJSON); err != nil {
		return 0, err
	}
	if err := rewriteLegacySourceBackedLayoutReferencesTx(ctx, tx, expected.ObjectAPIName, row.ID, expectedID, ownerPack, row); err != nil {
		return 0, err
	}

	if _, err := tx.ExecContext(ctx, `SET FOREIGN_KEY_CHECKS = 1`); err != nil {
		return 0, fmt.Errorf("restore foreign key checks for source-backed page_layout backfill %s/%s: %w", expected.ObjectAPIName, expected.LayoutName, err)
	}
	fkChecksRestored = true
	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("commit source-backed page_layout ID backfill for %s/%s: %w", expected.ObjectAPIName, expected.LayoutName, err)
	}
	return 1, nil
}

func loadLegacySourceBackedLayoutRewriteCandidate(
	ctx context.Context,
	db *sql.DB,
	objectAPIName, layoutName, ownerPack, canonicalID string,
) (*layoutownership.PageLayoutRow, error) {
	rows, err := db.QueryContext(ctx, `
SELECT Id, COALESCE(Config, ''), COALESCE(Source, ''), COALESCE(IsCustomized, 0), COALESCE(IsDefault, 0), COALESCE(OwnerPack, ''), COALESCE(IsDeleted, 0)
FROM _System_PageLayout
WHERE LOWER(ObjectApiName) = LOWER(?)
  AND LOWER(LayoutName) = LOWER(?)
ORDER BY CreatedDate ASC, Id ASC`, objectAPIName, layoutName)
	if err != nil {
		return nil, fmt.Errorf("query source-backed page_layout %s/%s by business key: %w", objectAPIName, layoutName, err)
	}
	defer rows.Close()

	var matches []layoutownership.PageLayoutRow
	for rows.Next() {
		var row layoutownership.PageLayoutRow
		if err := rows.Scan(&row.ID, &row.ConfigJSON, &row.Source, &row.IsCustomized, &row.IsDefault, &row.OwnerPack, &row.IsDeleted); err != nil {
			return nil, fmt.Errorf("scan source-backed page_layout %s/%s by business key: %w", objectAPIName, layoutName, err)
		}
		if strings.EqualFold(strings.TrimSpace(row.ID), canonicalID) {
			return nil, nil
		}
		if !layoutownership.IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
			if strings.TrimSpace(row.OwnerPack) == "" {
				return nil, fmt.Errorf("legacy source-backed page_layout %s/%s row %s has empty OwnerPack", objectAPIName, layoutName, row.ID)
			}
			if !strings.EqualFold(strings.TrimSpace(row.OwnerPack), ownerPack) {
				return nil, fmt.Errorf("legacy source-backed page_layout %s/%s row %s OwnerPack=%q want %q", objectAPIName, layoutName, row.ID, row.OwnerPack, ownerPack)
			}
		}
		matches = append(matches, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate source-backed page_layout %s/%s by business key: %w", objectAPIName, layoutName, err)
	}
	if len(matches) == 0 {
		return nil, nil
	}
	if len(matches) > 1 {
		ids := make([]string, 0, len(matches))
		for _, row := range matches {
			ids = append(ids, row.ID)
		}
		return nil, fmt.Errorf(
			"legacy source-backed page_layout %s/%s has %d live rows (%s); refusing ambiguous rewrite",
			objectAPIName, layoutName, len(matches), strings.Join(ids, ", "),
		)
	}
	return &matches[0], nil
}

func rewriteLegacySourceBackedPageLayoutIDTx(
	ctx context.Context,
	tx *sql.Tx,
	row *layoutownership.PageLayoutRow,
	canonicalID, objectAPIName, layoutName, configJSON string,
) error {
	if row == nil {
		return nil
	}
	result, err := tx.ExecContext(ctx, `
UPDATE _System_PageLayout
SET Id = ?, Config = ?, IsDeleted = 0, LastModifiedDate = CURRENT_TIMESTAMP
WHERE Id = ?
  AND LOWER(ObjectApiName) = LOWER(?)
  AND LOWER(LayoutName) = LOWER(?)`,
		canonicalID,
		configJSON,
		row.ID,
		objectAPIName,
		layoutName,
	)
	if err != nil {
		return fmt.Errorf("rewrite source-backed page_layout %s to canonical id %s: %w", row.ID, canonicalID, err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("rows affected rewriting source-backed page_layout %s: %w", row.ID, err)
	}
	if affected != 1 {
		return fmt.Errorf("rewrite source-backed page_layout %s to canonical id %s affected %d rows", row.ID, canonicalID, affected)
	}
	return nil
}

func rewriteLegacySourceBackedLayoutReferencesTx(
	ctx context.Context,
	tx *sql.Tx,
	objectAPIName, legacyID, canonicalID, ownerPack string,
	row *layoutownership.PageLayoutRow,
) error {
	if _, err := tx.ExecContext(ctx, `
UPDATE ProfileLayout
SET LayoutId = ?, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LayoutId = ?`,
		canonicalID,
		legacyID,
	); err != nil {
		return fmt.Errorf("rewrite ProfileLayout rows from %s to %s: %w", legacyID, canonicalID, err)
	}

	if _, err := tx.ExecContext(ctx, `
UPDATE _System_PageLayoutHistory
SET LayoutId = ?, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LayoutId = ?`,
		canonicalID,
		legacyID,
	); err != nil {
		return fmt.Errorf("rewrite _System_PageLayoutHistory rows from %s to %s: %w", legacyID, canonicalID, err)
	}

	if _, err := tx.ExecContext(ctx, `
UPDATE _System_LayoutAssignment
SET LayoutId = ?, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LayoutId = ?`,
		canonicalID,
		legacyID,
	); err != nil {
		return fmt.Errorf("rewrite _System_LayoutAssignment LayoutId rows from %s to %s: %w", legacyID, canonicalID, err)
	}

	if row != nil && !layoutownership.IsAdminOwnedPageLayout(row.IsCustomized, row.Source) {
		canonicalAssignmentID := sourceBackedLayoutAssignmentID(objectAPIName)
		canonicalAssignmentExists, err := sourceBackedWildcardAssignmentIDExistsTx(ctx, tx, canonicalAssignmentID)
		if err != nil {
			return fmt.Errorf("query canonical source-backed wildcard assignment %s: %w", canonicalAssignmentID, err)
		}
		if canonicalAssignmentExists {
			// LayoutId rewrite may have already converged the single wildcard row onto the canonical assignment ID.
			return nil
		}

		if _, err := tx.ExecContext(ctx, `
UPDATE _System_LayoutAssignment
SET Id = ?, LastModifiedDate = CURRENT_TIMESTAMP
WHERE LOWER(ObjectApiName) = LOWER(?)
  AND LayoutId = ?
  AND RoleId IS NULL
  AND ProfileId IS NULL
  AND RecordTypeId IS NULL
  AND LOWER(COALESCE(RouteSource, '')) = LOWER(?)
  AND LOWER(COALESCE(OwnerPack, '')) = LOWER(?)`,
			canonicalAssignmentID,
			objectAPIName,
			canonicalID,
			models.LayoutAssignmentRouteSourceManagedSeed,
			ownerPack,
		); err != nil {
			return fmt.Errorf("rewrite source-backed wildcard assignment ID for %s/%s: %w", objectAPIName, canonicalID, err)
		}
	}
	return nil
}

func canonicalizePageLayoutConfigID(rawConfig, canonicalID, objectAPIName string) (string, error) {
	layout, err := layoutownership.DecodePageLayoutConfig([]byte(rawConfig))
	if err != nil {
		return "", err
	}
	layout.ID = canonicalID
	if strings.TrimSpace(layout.ObjectAPIName) == "" {
		layout.ObjectAPIName = objectAPIName
	}
	canonical, err := json.Marshal(layout)
	if err != nil {
		return "", err
	}
	return string(canonical), nil
}

func sourceBackedPageLayoutIDExistsTx(ctx context.Context, tx *sql.Tx, layoutID string) (bool, error) {
	var count int
	if err := tx.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM _System_PageLayout
WHERE Id = ?
  AND (IsDeleted = 0 OR IsDeleted IS NULL)`, layoutID).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}

func sourceBackedWildcardAssignmentIDExistsTx(ctx context.Context, tx *sql.Tx, assignmentID string) (bool, error) {
	var count int
	if err := tx.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM _System_LayoutAssignment
WHERE Id = ?`, assignmentID).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}