package controlplane

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

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

// ReconcileManagedSystemObjectVisibility restores bootstrap-owned system object
// rows that were soft-deleted in the metadata table.
//
// Fresh installs already seed these rows. This reconcile exists to recover
// environments that drifted into a state where a managed system object such as
// `_System_File` still has its physical table but is hidden from describe calls
// because `_System_Object.IsDeleted = 1`.
func ReconcileManagedSystemObjectVisibility(rawDB *sql.DB, tableDefs []schema.TableDefinition) error {
	return reconcileManagedSystemObjectVisibility(rawDB, tableDefs)
}

func reconcileManagedSystemObjectVisibility(rawDB *sql.DB, tableDefs []schema.TableDefinition) error {
	updated := 0
	for _, def := range tableDefs {
		if def.TableName == "" {
			continue
		}

		result, err := rawDB.Exec(
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = 0, `%s` = NOW() WHERE LOWER(`%s`) = ? AND COALESCE(`%s`, 0) != 0",
				constants.TableObject,
				constants.FieldIsDeleted,
				constants.FieldLastModifiedDate,
				constants.FieldAPIName,
				constants.FieldIsDeleted,
			),
			strings.ToLower(def.TableName),
		)
		if err != nil {
			return fmt.Errorf("restore managed object %s visibility: %w", def.TableName, err)
		}
		if affected, err := result.RowsAffected(); err == nil {
			updated += int(affected)
		}
	}

	if updated > 0 {
		log.Printf("   🔓 Restored %d soft-deleted managed object(s) in _System_Object", updated)
	}

	return nil
}