package persistence

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

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

const (
	permissionAuthoringAuditDisplayColumn        = constants.FieldSysSetupAuditTrail_Display
	permissionAuthoringAuditDisplayInspectionSQL = `SELECT COLUMN_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`
	permissionAuthoringAuditDisplayWidenSQL = "ALTER TABLE `_System_SetupAuditTrail` MODIFY COLUMN `Display` MEDIUMTEXT NULL"
)

func declaresPermissionAuthoringAuditDisplayCapacity(def schema.TableDefinition) bool {
	if def.TableName != constants.TableSetupAuditTrail {
		return false
	}
	for _, column := range def.Columns {
		if column.Name == permissionAuthoringAuditDisplayColumn {
			return strings.EqualFold(column.Type, "MEDIUMTEXT") && column.Nullable
		}
	}
	return false
}

// reconcilePermissionAuthoringAuditDisplayCapacity is the warm-schema half of
// the aggregate audit cutover. Generic text compatibility intentionally avoids
// churn between TEXT families, so this exact migration owns the required
// capacity increase for existing installations.
func (r *SchemaRepository) reconcilePermissionAuthoringAuditDisplayCapacity(ctx context.Context) error {
	columnType, nullable, err := inspectPermissionAuthoringAuditDisplay(ctx, r.db)
	if err != nil {
		return fmt.Errorf("reconcile permission authoring audit display capacity: %w", err)
	}
	if nullable == "YES" && (columnType == "mediumtext" || columnType == "longtext") {
		return nil
	}
	if columnType != "text" || nullable != "YES" {
		return fmt.Errorf(
			"reconcile permission authoring audit display capacity: physical %s.%s has incompatible shape (%s/nullable=%s)",
			constants.TableSetupAuditTrail,
			permissionAuthoringAuditDisplayColumn,
			columnType,
			nullable,
		)
	}
	if _, err := r.db.ExecContext(ctx, permissionAuthoringAuditDisplayWidenSQL); err != nil {
		return fmt.Errorf("reconcile permission authoring audit display capacity: widen exact legacy column: %w", err)
	}
	columnType, nullable, err = inspectPermissionAuthoringAuditDisplay(ctx, r.db)
	if err != nil {
		return fmt.Errorf("reconcile permission authoring audit display capacity: verify migrated column: %w", err)
	}
	if nullable != "YES" || columnType != "mediumtext" {
		return fmt.Errorf(
			"reconcile permission authoring audit display capacity: migrated %s.%s has incompatible shape (%s/nullable=%s)",
			constants.TableSetupAuditTrail,
			permissionAuthoringAuditDisplayColumn,
			columnType,
			nullable,
		)
	}
	return nil
}

func inspectPermissionAuthoringAuditDisplay(ctx context.Context, db *sql.DB) (string, string, error) {
	var columnType, nullable string
	if err := db.QueryRowContext(
		ctx,
		permissionAuthoringAuditDisplayInspectionSQL,
		constants.TableSetupAuditTrail,
		permissionAuthoringAuditDisplayColumn,
	).Scan(&columnType, &nullable); err != nil {
		return "", "", err
	}
	return strings.ToLower(columnType), nullable, nil
}