package connectorreaders

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

	seedrows "github.com/pingcap-inc/nexuscrm-contract/pkg/seedrows"
)

// tableActionSchema is the physical table backing standard-action input/output
// descriptors. Inlined here (exact value "_System_ActionSchema") to keep this
// package free of any up-DAG dependency; it mirrors the bootstrap local const of
// the same name.
const tableActionSchema = "_System_ActionSchema"

// ActionSchemaRow is the subset of _System_ActionSchema consumed by the generic
// standard-action dispatcher at request time. It is the contract seedrows type;
// re-exported here for the relocated reader.
type ActionSchemaRow = seedrows.ActionSchemaRow

// GetActionSchema returns every active input/output descriptor for a
// standard-action Id, sorted by (Direction, SortOrder, APIName).
func GetActionSchema(db *sql.DB, standardActionID string) ([]ActionSchemaRow, error) {
	query := fmt.Sprintf(
		`SELECT Direction, APIName, DataType, ReferenceTo, Label, UISection, UIMode,
		        TargetObject, TargetFieldAPIName, PrefillSourceFieldAPIName, RequiredPicklistTag, MappingSourceObject, MappingActionType,
		        IsCollection, IsRequired, SortOrder
		   FROM %s
		  WHERE StandardActionId = ? AND IsActive = 1 AND IsDeleted = 0
		  ORDER BY Direction ASC, SortOrder ASC, APIName ASC`,
		tableActionSchema,
	)
	rows, err := db.Query(query, standardActionID)
	if err != nil {
		return nil, fmt.Errorf("query action schema: %w", err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return nil, fmt.Errorf("read action schema columns: %w", err)
	}

	var out []ActionSchemaRow
	for rows.Next() {
		var row ActionSchemaRow
		values := make([]sql.NullString, len(cols))
		scanArgs := make([]interface{}, len(cols))
		for i := range values {
			scanArgs[i] = &values[i]
		}
		if err := rows.Scan(scanArgs...); err != nil {
			return nil, fmt.Errorf("scan action schema row: %w", err)
		}
		for i, col := range cols {
			if !values[i].Valid {
				continue
			}
			assignActionSchemaColumn(&row, col, values[i].String)
		}
		out = append(out, row)
	}
	return out, rows.Err()
}

// GetActionSchemas returns active input/output descriptors for several
// standard-action IDs in one DB round trip, grouped by StandardActionId. Row
// ordering within each group matches GetActionSchema.
func GetActionSchemas(db *sql.DB, standardActionIDs []string) (map[string][]ActionSchemaRow, error) {
	actionIDs := uniqueNonBlankStrings(standardActionIDs)
	out := make(map[string][]ActionSchemaRow, len(actionIDs))
	for _, id := range actionIDs {
		out[id] = nil
	}
	if db == nil || len(actionIDs) == 0 {
		return out, nil
	}
	query := fmt.Sprintf(
		`SELECT StandardActionId, Direction, APIName, DataType, ReferenceTo, Label, UISection, UIMode,
		        TargetObject, TargetFieldAPIName, PrefillSourceFieldAPIName, RequiredPicklistTag, MappingSourceObject, MappingActionType,
		        IsCollection, IsRequired, SortOrder
		   FROM %s
		  WHERE StandardActionId IN (%s) AND IsActive = 1 AND IsDeleted = 0
		  ORDER BY StandardActionId ASC, Direction ASC, SortOrder ASC, APIName ASC`,
		tableActionSchema,
		sqlPlaceholders(len(actionIDs)),
	)
	args := make([]interface{}, len(actionIDs))
	for i, id := range actionIDs {
		args[i] = id
	}
	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("query action schema batch: %w", err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return nil, fmt.Errorf("read action schema columns: %w", err)
	}
	for rows.Next() {
		var actionID sql.NullString
		values := make([]sql.NullString, len(cols)-1)
		scanArgs := make([]interface{}, len(cols))
		scanArgs[0] = &actionID
		for i := range values {
			scanArgs[i+1] = &values[i]
		}
		if err := rows.Scan(scanArgs...); err != nil {
			return nil, fmt.Errorf("scan action schema row: %w", err)
		}
		if !actionID.Valid {
			continue
		}
		var row ActionSchemaRow
		for i, col := range cols[1:] {
			if !values[i].Valid {
				continue
			}
			assignActionSchemaColumn(&row, col, values[i].String)
		}
		out[actionID.String] = append(out[actionID.String], row)
	}
	return out, rows.Err()
}

func uniqueNonBlankStrings(values []string) []string {
	out := make([]string, 0, len(values))
	seen := make(map[string]struct{}, len(values))
	for _, value := range values {
		value = strings.TrimSpace(value)
		if value == "" {
			continue
		}
		if _, ok := seen[value]; ok {
			continue
		}
		seen[value] = struct{}{}
		out = append(out, value)
	}
	return out
}

func sqlPlaceholders(n int) string {
	if n <= 0 {
		return ""
	}
	return strings.TrimRight(strings.Repeat("?,", n), ",")
}

func assignActionSchemaColumn(row *ActionSchemaRow, col string, value string) {
	switch strings.ToLower(col) {
	case "direction":
		row.Direction = value
	case "apiname":
		row.APIName = value
	case "datatype":
		row.DataType = value
	case "referenceto":
		row.ReferenceTo = value
	case "label":
		row.Label = value
	case "uisection":
		row.UISection = value
	case "uimode":
		row.UIMode = value
	case "targetobject":
		row.TargetObject = value
	case "targetfieldapiname":
		row.TargetFieldAPIName = value
	case "prefillsourcefieldapiname":
		row.PrefillSourceFieldAPIName = value
	case "requiredpicklisttag":
		row.RequiredPicklistTag = value
	case "mappingsourceobject":
		row.MappingSourceObject = value
	case "mappingactiontype":
		row.MappingActionType = value
	case "iscollection":
		row.IsCollection = value == "1" || strings.EqualFold(value, "true")
	case "isrequired":
		row.IsRequired = value == "1" || strings.EqualFold(value, "true")
	case "sortorder":
		if n, err := strconv.Atoi(value); err == nil {
			row.SortOrder = n
		}
	}
}