package persistence

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

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// Schema Queries

// GetSchemaByAPIName queries a single schema by API name
func (r *MetadataRepository) GetSchemaByAPIName(ctx context.Context, apiName string) (*models.ObjectMetadata, error) {
	executor := r.GetExecutor(ctx)
	loadObject := func(sqlText string, params ...interface{}) (*models.ObjectMetadata, error) {
		row := executor.QueryRowContext(ctx, sqlText, params...)
		return r.scanObject(row)
	}

	qr := query.From(constants.TableObject).
		Select(systemObjectColumns()).
		Where(constants.FieldAPIName, apiName).
		Where(constants.FieldIsDeleted, false).
		Build()

	obj, err := loadObject(qr.SQL, qr.Params...)
	if err == sql.ErrNoRows {
		cols := systemObjectColumns()
		selectCols := make([]string, 0, len(cols))
		for _, col := range cols {
			selectCols = append(selectCols, fmt.Sprintf("`%s`", col))
		}

		caseInsensitiveSQL := fmt.Sprintf(
			"SELECT %s FROM `%s` WHERE LOWER(`%s`) = LOWER(?) AND `%s` = ? LIMIT 1",
			strings.Join(selectCols, ", "),
			constants.TableObject,
			constants.FieldAPIName,
			constants.FieldIsDeleted,
		)
		obj, err = loadObject(caseInsensitiveSQL, apiName, false)
	}
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, apperrors.NewInternalError("failed to scan object", err)
	}

	obj.Fields, err = r.GetFieldsForObject(ctx, obj.ID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to load fields", err)
	}

	models.RehydratePolymorphicDiscriminatorFields(obj)

	// Populate FieldsMap
	obj.FieldsMap = make(map[string]*models.FieldMetadata)
	for i := range obj.Fields {
		f := &obj.Fields[i]
		obj.FieldsMap[strings.ToLower(f.APIName)] = f
	}

	return obj, nil
}

// GetAllSchemas queries all schemas
func (r *MetadataRepository) GetAllSchemas(ctx context.Context) ([]*models.ObjectMetadata, error) {
	executor := r.GetExecutor(ctx)
	qr := query.From(constants.TableObject).
		Select(systemObjectColumns()).
		Where(constants.FieldIsDeleted, false).
		Build()

	rows, err := executor.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query objects", err)
	}
	defer rows.Close()

	schemas := make([]*models.ObjectMetadata, 0)
	idToSchema := make(map[string]*models.ObjectMetadata)

	for rows.Next() {
		obj, err := r.scanObject(rows)
		if err != nil {
			return nil, err
		}
		schemas = append(schemas, obj)
		idToSchema[strings.ToLower(obj.ID)] = obj
	}
	rows.Close()
	if err = rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating object rows", err)
	}

	fQr := query.From(constants.TableField).
		Select(constants.ColsSystemField).
		Where(constants.FieldIsDeleted, false).
		Build()

	fieldRows, err := executor.QueryContext(ctx, fQr.SQL, fQr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query fields", err)
	}
	defer func() { _ = fieldRows.Close() }()

	for fieldRows.Next() {
		field, objectID, err := r.scanField(fieldRows)
		if err != nil {
			return nil, apperrors.NewInternalError("failed to scan field in complete schema read", err)
		}

		if obj, ok := idToSchema[strings.ToLower(objectID)]; ok {
			obj.Fields = append(obj.Fields, *field)
		}
	}
	if err := fieldRows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating field rows", err)
	}

	for _, obj := range schemas {
		models.RehydratePolymorphicDiscriminatorFields(obj)
		obj.FieldsMap = make(map[string]*models.FieldMetadata, len(obj.Fields))
		for i := range obj.Fields {
			f := &obj.Fields[i]
			obj.FieldsMap[strings.ToLower(f.APIName)] = f
		}
	}

	return schemas, nil
}

// GetFieldsForObject queries fields for a specific object ID
func (r *MetadataRepository) GetFieldsForObject(ctx context.Context, objectID string) ([]models.FieldMetadata, error) {
	// Use Builder
	qr := query.From(constants.TableField).
		Select(constants.ColsSystemField).
		Where(constants.FieldObjectID, objectID).
		Where(constants.FieldIsDeleted, false).
		Build()

	rows, err := r.GetExecutor(ctx).QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query fields", err)
	}
	defer rows.Close()

	fields := make([]models.FieldMetadata, 0)
	for rows.Next() {
		field, _, err := r.scanField(rows)
		if err != nil {
			log.Printf("⚠️ Failed to scan field: %v", err)
			continue
		}
		fields = append(fields, *field)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating fields", err)
	}
	return fields, nil
}

// GetRecordTypes queries record types for an object
func (r *MetadataRepository) GetRecordTypes(ctx context.Context, objectAPIName string) ([]*models.RecordType, error) {
	return r.queryRecordTypes(ctx, &objectAPIName)
}

// GetAllRecordTypes queries the complete effective record-type set in one
// snapshot read. Catalog publication groups the result by object in memory so
// the immutable snapshot does not require one remote query per object.
func (r *MetadataRepository) GetAllRecordTypes(ctx context.Context) ([]*models.RecordType, error) {
	return r.queryRecordTypes(ctx, nil)
}

func (r *MetadataRepository) queryRecordTypes(ctx context.Context, objectAPIName *string) ([]*models.RecordType, error) {
	executor := r.GetExecutor(ctx)
	// RecordType's canonical read shape follows Salesforce: rows are scoped by
	// SobjectType, not by a synthetic ObjectId foreign key.
	cols := []string{
		constants.FieldID, "SobjectType", constants.FieldSysRecordType_Name,
		constants.FieldSysRecordType_DeveloperName,
		constants.FieldSysRecordType_Description, constants.FieldSysRecordType_IsActive,
		constants.FieldSysRecordType_IsMaster, constants.FieldSysRecordType_BusinessProcessID,
		constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}

	selectCols := make([]string, 0, len(cols))
	for _, col := range cols {
		selectCols = append(selectCols, fmt.Sprintf("`%s`", col))
	}
	where := fmt.Sprintf("`%s` = ?", constants.FieldIsDeleted)
	params := []any{false}
	if objectAPIName != nil {
		where = fmt.Sprintf("LOWER(`SobjectType`) = LOWER(?) AND `%s` = ?", constants.FieldIsDeleted)
		params = []any{*objectAPIName, false}
	}
	sqlText := fmt.Sprintf("SELECT %s FROM `%s` WHERE %s",
		strings.Join(selectCols, ", "), constants.TableRecordType, where)
	rows, err := executor.QueryContext(ctx, sqlText, params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query record types", err)
	}
	defer rows.Close()

	types := make([]*models.RecordType, 0)
	for rows.Next() {
		var rt models.RecordType
		var developerName sql.NullString
		var description sql.NullString
		var businessProcessID sql.NullString
		var isActive, isMaster sql.NullBool
		var sobjectType string

		// Scan matching the selected columns
		if err := rows.Scan(
			&rt.ID, &sobjectType, &rt.Name, &developerName,
			&description, &isActive, &isMaster, &businessProcessID,
			&rt.CreatedDate, &rt.LastModifiedDate,
		); err != nil {
			return nil, apperrors.NewInternalError("failed to scan record type", err)
		}

		rt.ObjectAPIName = strings.TrimSpace(sobjectType)
		if rt.ObjectAPIName == "" && objectAPIName != nil {
			rt.ObjectAPIName = *objectAPIName
		}
		rt.Label = rt.Name
		if developerName.Valid {
			rt.DeveloperName = developerName.String
		}

		desc := ""
		if description.Valid {
			desc = description.String
		}
		rt.Description = &desc

		if businessProcessID.Valid {
			rt.BusinessProcessID = &businessProcessID.String
		}

		rt.IsActive = isActive.Bool
		// rt.IsMaster = isMaster.Bool // If struct has IsMaster

		types = append(types, &rt)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating record types", err)
	}
	return types, nil
}

// GetScopedPicklistValues queries scoped picklist values for a record-type/field tuple.
func (r *MetadataRepository) GetScopedPicklistValues(ctx context.Context, recordTypeID, fieldID string) ([]models.ScopedPicklistValue, error) {
	cols := []string{
		constants.FieldSysRecordTypePicklistValue_Value,
		constants.FieldSysRecordTypePicklistValue_IsActive,
		constants.FieldSysRecordTypePicklistValue_IsDefault,
		constants.FieldSysRecordTypePicklistValue_SortOrder,
		constants.FieldSysRecordTypePicklistValue_OwnerPack,
		constants.FieldSysRecordTypePicklistValue_IsCustomized,
	}
	// SkipID is required: the explicit `cols` list above is exactly six
	// columns and Scan below binds exactly six destinations. Without
	// SkipID the builder prepends `Id`, the query then selects seven
	// columns, and `Scan` fails at runtime with "expected 7 destination
	// arguments in Scan, not 6" — meaning every call to this helper
	// would return an error in production. The real-TiDB metadata read outcome
	// test exercises this query, scan arity, tuple scope, deletion filter, and
	// ordering together without pinning the generated SQL text.
	qr := query.From(constants.TableRecordTypePicklistValue).
		SkipID().
		Select(cols).
		Where(constants.FieldSysRecordTypePicklistValue_RecordTypeID, recordTypeID).
		Where(constants.FieldSysRecordTypePicklistValue_FieldID, fieldID).
		Where(constants.FieldIsDeleted, false).
		OrderBy(constants.FieldSysRecordTypePicklistValue_SortOrder, "ASC").
		AddOrderBy(constants.FieldSysRecordTypePicklistValue_Value, "ASC").
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query scoped picklist values", err)
	}
	defer rows.Close()

	values := make([]models.ScopedPicklistValue, 0)
	for rows.Next() {
		var value models.ScopedPicklistValue
		var ownerPack sql.NullString
		if err := rows.Scan(
			&value.Value,
			&value.IsActive,
			&value.IsDefault,
			&value.SortOrder,
			&ownerPack,
			&value.IsCustomized,
		); err != nil {
			return nil, apperrors.NewInternalError("failed to scan scoped picklist value", err)
		}
		if ownerPack.Valid {
			value.OwnerPack = strings.TrimSpace(ownerPack.String)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating scoped picklist values", err)
	}
	return values, nil
}

func scanRelationships(rows *sql.Rows) ([]*models.Relationship, error) {
	rels := make([]*models.Relationship, 0)
	for rows.Next() {
		var rel models.Relationship
		var relatedListLabel, relatedListFields sql.NullString
		var isSharingParent, cascadeDelete, restrictedDelete sql.NullBool

		if err := rows.Scan(
			&rel.ID, &rel.ChildObjectAPIName, &rel.ParentObjectAPIName, &rel.FieldAPIName,
			&rel.RelationshipName, &rel.RelationshipType, &isSharingParent, &cascadeDelete, &restrictedDelete,
			&relatedListLabel, &relatedListFields, &rel.CreatedDate, &rel.LastModifiedDate,
		); err != nil {
			log.Printf("Warning: Failed to scan relationship: %v", err)
			continue
		}

		if relatedListLabel.Valid {
			rel.RelatedListLabel = &relatedListLabel.String
		}
		if relatedListFields.Valid {
			rel.RelatedListFields = &relatedListFields.String
		}
		if isSharingParent.Valid {
			rel.IsSharingParent = isSharingParent.Bool
		}
		if cascadeDelete.Valid {
			rel.CascadeDelete = cascadeDelete.Bool
		}
		if restrictedDelete.Valid {
			rel.RestrictedDelete = restrictedDelete.Bool
		}

		rels = append(rels, &rel)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating relationships", err)
	}
	return rels, nil
}

// GetAllRelationships queries all explicit relationships in one round trip.
func (r *MetadataRepository) GetAllRelationships(ctx context.Context) ([]*models.Relationship, error) {
	executor := r.GetExecutor(ctx)
	cols := []string{
		constants.FieldID, constants.FieldSysRelationship_ChildObjectAPIName, constants.FieldSysRelationship_ParentObjectAPIName,
		constants.FieldSysRelationship_FieldAPIName, constants.FieldSysRelationship_RelationshipName,
		constants.FieldSysRelationship_RelationshipType, constants.FieldSysRelationship_IsSharingParent, constants.FieldSysRelationship_CascadeDelete,
		constants.FieldSysRelationship_RestrictedDelete, constants.FieldSysRelationship_RelatedListLabel,
		constants.FieldSysRelationship_RelatedListFields, constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}

	qr := query.From(constants.TableRelationship).
		Select(cols).
		Build()

	rows, err := executor.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	return scanRelationships(rows)
}

// GetRelationships queries relationships for a child object
func (r *MetadataRepository) GetRelationships(ctx context.Context, childObjectAPIName string) ([]*models.Relationship, error) {
	cols := []string{
		constants.FieldID, constants.FieldSysRelationship_ChildObjectAPIName, constants.FieldSysRelationship_ParentObjectAPIName,
		constants.FieldSysRelationship_FieldAPIName, constants.FieldSysRelationship_RelationshipName,
		constants.FieldSysRelationship_RelationshipType, constants.FieldSysRelationship_IsSharingParent, constants.FieldSysRelationship_CascadeDelete,
		constants.FieldSysRelationship_RestrictedDelete, constants.FieldSysRelationship_RelatedListLabel,
		constants.FieldSysRelationship_RelatedListFields, constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}

	qr := query.From(constants.TableRelationship).
		Select(cols).
		Where(constants.FieldSysRelationship_ChildObjectAPIName, childObjectAPIName).
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	return scanRelationships(rows)
}

// GetFieldDependencies queries field dependencies for an object
func (r *MetadataRepository) GetFieldDependencies(ctx context.Context, objectAPIName string) ([]*models.FieldDependency, error) {
	// 1. Resolve Object ID
	var objectID string
	err := r.db.QueryRowContext(ctx, fmt.Sprintf("SELECT %s FROM %s WHERE %s = ?", constants.ColID, constants.TableObject, constants.FieldSysObject_APIName), objectAPIName).Scan(&objectID)
	if err != nil {
		if err == sql.ErrNoRows {
			return []*models.FieldDependency{}, nil
		}
		return nil, apperrors.NewInternalError("failed to resolve object id", err)
	}

	// 2. Query Field Dependencies via JOIN with _System_Field to filter by Object
	// Schema: id, controlling_field_id, dependent_field_id, controlling_value, dependent_values
	// 2. Query Field Dependencies via JOIN with _System_Field to filter by Object
	// Schema: id, controlling_field_id, dependent_field_id, controlling_value, dependent_values
	// Note: We avoid "d." prefix as Builder handles `TableFieldDependency`
	cols := []string{
		constants.FieldID,
		constants.FieldSysFieldDependency_ControllingFieldID,
		constants.FieldSysFieldDependency_DependentFieldID,
		constants.FieldSysFieldDependency_ControllingValue,
		constants.FieldSysFieldDependency_DependentValues,
		constants.FieldCreatedDate,
		constants.FieldLastModifiedDate,
	}

	// Join with Field table to filter by object_id
	// d.dependent_field_id = f.id
	joinOn := fmt.Sprintf("`%s`.`%s` = f.`%s`",
		constants.TableFieldDependency, constants.FieldSysFieldDependency_DependentFieldID,
		constants.FieldID)

	qr := query.From(constants.TableFieldDependency).
		Select(cols).
		Join("JOIN", constants.TableField, "f", joinOn).
		Where(fmt.Sprintf("f.`%s`", constants.FieldObjectID), objectID).
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	deps := make([]*models.FieldDependency, 0)
	for rows.Next() {
		var dep models.FieldDependency
		var controllingValue, dependentValues sql.NullString

		if err := rows.Scan(
			&dep.ID, &dep.ControllingField, &dep.DependentField,
			&controllingValue, &dependentValues,
			&dep.CreatedDate, &dep.LastModifiedDate,
		); err != nil {
			log.Printf("Warning: Failed to scan field dependency: %v", err)
			continue
		}

		dep.ObjectAPIName = objectAPIName // Manual set

		if controllingValue.Valid {
			dep.ControllingValue = controllingValue.String
		}

		if dependentValues.Valid && dependentValues.String != "" {
			if err := json.Unmarshal([]byte(dependentValues.String), &dep.DependentValues); err != nil {
				log.Printf("Warning: Failed to unmarshal dependent values for %s: %v", dep.ID, err)
			}
		}

		deps = append(deps, &dep)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating field dependencies", err)
	}
	return deps, nil
}

// GetChildRelationships returns fields on OTHER objects that lookup to this object
func (r *MetadataRepository) GetChildRelationships(ctx context.Context, parentObjectAPIName string) ([]*models.ObjectMetadata, error) {
	qr := query.From(constants.TableField).
		SkipID().
		Select([]string{fmt.Sprintf("`o`.`%s`", constants.FieldSysObject_APIName)}).
		Join("JOIN", constants.TableObject, "o",
			fmt.Sprintf("`%s`.`%s` = `o`.`%s`", constants.TableField, constants.FieldObjectID, constants.ColSysObject_ID)).
		WhereRaw(fmt.Sprintf("(`%s`.`%s` = ? OR `%s`.`%s` LIKE ?) AND `%s`.`%s` = 'reference'",
			constants.TableField, constants.FieldReferenceTo,
			constants.TableField, constants.FieldReferenceTo,
			constants.TableField, constants.FieldType),
			[]interface{}{parentObjectAPIName, "%" + parentObjectAPIName + "%"}).
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var children []*models.ObjectMetadata
	for rows.Next() {
		var apiName string
		if err := rows.Scan(&apiName); err != nil {
			log.Printf("Warning: Failed to scan child relationship: %v", err)
			continue
		}

		// Load full schema for child
		if schema, err := r.GetSchemaByAPIName(ctx, apiName); err == nil && schema != nil {
			children = append(children, schema)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating child relationships", err)
	}
	return children, nil
}

// ConfigureAutoNumberField implements MetadataRepository.ConfigureAutoNumberField.
//
// Two-step idempotent upsert:
//  1. UPDATE `_System_Field.auto_number_format / scope / start` for the
//     target (objectAPIName, fieldAPIName).
//  2. INSERT ... ON DUPLICATE KEY UPDATE on `_System_AutoNumberCounter`
//     with scope_key=” (global) and NextValue = max(existing, start).
//
// Both statements run outside a transaction — ordering matters only in
// that the engine's AssignOne looks up the counter row before running
// the reservation, so step 2 MUST succeed for step 1 to be usable.
// Step 2 is idempotent (re-seed-safe); step 1 is a no-op when the field
// is already configured identically.
//
// Replaces the deleted UpsertAutoNumber (PRD §7.2 step k).
func (r *MetadataRepository) ConfigureAutoNumberField(
	ctx context.Context,
	objectAPIName, fieldAPIName, format string,
	start int64,
) error {
	if start < 1 {
		start = 1
	}

	// Step 1 — field metadata. Match the generator's idempotency idiom
	// (packs.applyAutoNumberFormats): only UPDATE when values actually
	// differ.
	const fieldUpdate = "UPDATE `_System_Field` f " +
		"JOIN `_System_Object` o ON o.Id = f.ObjectId " +
		"SET f.auto_number_format = ?, f.auto_number_scope = 'global', f.auto_number_start = ?, f.LastModifiedDate = NOW() " +
		"WHERE o.ApiName = ? AND f.ApiName = ? " +
		"AND (f.auto_number_format IS NULL OR f.auto_number_format <> ? " +
		"     OR f.auto_number_scope IS NULL OR f.auto_number_scope <> 'global' " +
		"     OR f.auto_number_start IS NULL OR f.auto_number_start <> ?)"
	if _, err := r.db.ExecContext(ctx, fieldUpdate, format, start, objectAPIName, fieldAPIName, format, start); err != nil {
		return fmt.Errorf("configure autonumber field %s.%s metadata: %w", objectAPIName, fieldAPIName, err)
	}

	// Resolve fieldID for the counter upsert.
	var fieldID string
	err := r.db.QueryRowContext(ctx,
		"SELECT f.Id FROM `_System_Field` f "+
			"JOIN `_System_Object` o ON o.Id = f.ObjectId "+
			"WHERE o.ApiName = ? AND f.ApiName = ?",
		objectAPIName, fieldAPIName,
	).Scan(&fieldID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			// Field row does not exist — caller just added the column
			// metadata; skip counter seed and let the next metadata
			// refresh re-seed. Non-fatal.
			return nil
		}
		return fmt.Errorf("configure autonumber field %s.%s resolve id: %w", objectAPIName, fieldAPIName, err)
	}

	// Step 2 — counter seed. Row id matches the engine's
	// generateCounterRowID("counter::<fieldID>::<scopeKey>") so SeedOne
	// / AssignOne converge on the same row.
	counterRowID := "counter::" + fieldID + "::"
	const counterUpsert = "INSERT INTO `_System_AutoNumberCounter` " +
		"(`Id`, `FieldId`, `ScopeKey`, `NextValue`, `CreatedDate`, `LastModifiedDate`) " +
		"VALUES (?, ?, '', ?, NOW(6), NOW(6)) " +
		"ON DUPLICATE KEY UPDATE `NextValue` = GREATEST(`NextValue`, VALUES(`NextValue`)), " +
		"`LastModifiedDate` = VALUES(`LastModifiedDate`)"
	if _, err := r.db.ExecContext(ctx, counterUpsert, counterRowID, fieldID, start); err != nil {
		return fmt.Errorf("configure autonumber field %s.%s counter seed: %w", objectAPIName, fieldAPIName, err)
	}
	return nil
}