package persistence

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

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/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) {
	loadObject := func(sqlText string, params ...interface{}) (*models.ObjectMetadata, error) {
		row := r.db.QueryRowContext(ctx, sqlText, params...)
		return r.scanObject(row)
	}

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

	obj, err := loadObject(qr.SQL, qr.Params...)
	if err == sql.ErrNoRows {
		selectCols := make([]string, 0, len(constants.ColsSystemObject))
		for _, col := range constants.ColsSystemObject {
			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)
	}

	// Apply aliasing based on object type
	for i := range obj.Fields {
		r.applyFieldAliasing(&obj.Fields[i], obj.IsCustom)
	}

	// 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) {
	qr := query.From(constants.TableObject).
		Select(constants.ColsSystemObject).
		Where(constants.FieldIsDeleted, false).
		Build()

	rows, err := r.db.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 := r.db.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 {
			log.Printf("⚠️ Failed to scan field: %v", err)
			continue
		}

		if obj, ok := idToSchema[strings.ToLower(objectID)]; ok {
			r.applyFieldAliasing(field, obj.IsCustom)
			obj.Fields = append(obj.Fields, *field)
			// Populate Map immediately
			if obj.FieldsMap == nil {
				obj.FieldsMap = make(map[string]*models.FieldMetadata)
			}
			obj.FieldsMap[strings.ToLower(field.APIName)] = &obj.Fields[len(obj.Fields)-1]
		}
	}
	if err := fieldRows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating field rows", err)
	}

	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.db.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) {
	// 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.RecordType{}, nil
		}
		return nil, apperrors.NewInternalError("failed to resolve object id", err)
	}

	// 2. Query Record Types by Object ID
	// Note: _System_RecordType has columns: id, object_id, name, description, is_active, is_master
	// 2. Query Record Types by Object ID
	cols := []string{
		constants.FieldID, constants.FieldObjectID, constants.FieldSysRecordType_Name,
		constants.FieldSysRecordType_Description, constants.FieldSysRecordType_IsActive,
		constants.FieldSysRecordType_IsMaster,
		constants.FieldCreatedDate, constants.FieldLastModifiedDate,
	}

	qr := query.From(constants.TableRecordType).
		Select(cols).
		Where(constants.FieldObjectID, objectID).
		Build()

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

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

		// Scan matching the selected columns
		if err := rows.Scan(
			&rt.ID, &objID, &rt.Name,
			&description, &isActive, &isMaster,
			&rt.CreatedDate, &rt.LastModifiedDate,
		); err != nil {
			log.Printf("Warning: Failed to scan record type: %v", err)
			continue
		}

		rt.ObjectAPIName = objectAPIName // Set from argument
		rt.Label = rt.Name               // Set Label to Name as table has no separate Label

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

		// BusinessProcessID does not exist in schema, leaving nil

		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
}

// GetAutoNumbers is retained for interface compatibility during the
// autonumber v2 cutover (PRD §7.2). The legacy `_System_AutoNumber` table
// has been dropped; this implementation always returns an empty slice.
// Callers should migrate to the autonumber.Engine + autonumber.MetadataSource
// introduced in Wave 4 Lane D for runtime assignment.
func (r *MetadataRepository) GetAutoNumbers(_ context.Context, _ string) ([]*models.AutoNumber, error) {
	return []*models.AutoNumber{}, nil
}

// GetAllAutoNumbers is retained for interface compatibility; see
// GetAutoNumbers for details.
func (r *MetadataRepository) GetAllAutoNumbers(_ context.Context) ([]*models.AutoNumber, error) {
	return []*models.AutoNumber{}, nil
}

// GetAllIdentifierRules is retained for interface compatibility during the
// autonumber v2 cutover. The legacy `_System_IdentifierRule` table has been
// dropped; its functionality is now covered by the autonumber engine
// driven by `_System_Field.auto_number_*` columns. Returns an empty slice.
func (r *MetadataRepository) GetAllIdentifierRules(_ context.Context) ([]*models.IdentifierRule, error) {
	return []*models.IdentifierRule{}, 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) {
	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 := r.db.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
}