package persistence

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

	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"
)

// GetApprovalLayouts queries all approval layouts for an object
func (r *MetadataRepository) GetApprovalLayouts(ctx context.Context, objectAPIName string) ([]*models.ApprovalPageLayout, error) {
	qr := query.From(constants.TableApprovalPageLayout).
		Select(constants.ColsSystemApprovalPageLayout).
		Where(constants.FieldObjectAPIName, objectAPIName).
		Where(constants.FieldIsDeleted, false).
		OrderBy(constants.FieldSysApprovalPageLayout_IsDefault, "DESC").
		Build()

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

	layouts := make([]*models.ApprovalPageLayout, 0)
	for rows.Next() {
		layout, err := r.scanApprovalLayout(rows)
		if err != nil {
			return nil, err
		}
		layouts = append(layouts, layout)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating approval layouts", err)
	}
	return layouts, nil
}

// GetApprovalLayout queries a single approval layout by ID (excludes soft-deleted)
func (r *MetadataRepository) GetApprovalLayout(ctx context.Context, layoutID string) (*models.ApprovalPageLayout, error) {
	qr := query.From(constants.TableApprovalPageLayout).
		Select(constants.ColsSystemApprovalPageLayout).
		Where(constants.FieldID, layoutID).
		Where(constants.FieldIsDeleted, false).
		Build()

	layout, err := r.scanApprovalLayout(r.db.QueryRowContext(ctx, qr.SQL, qr.Params...))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return layout, nil
}

// SaveApprovalLayout saves or updates an approval page layout
func (r *MetadataRepository) SaveApprovalLayout(ctx context.Context, layout *models.ApprovalPageLayout) error {
	configJSON, err := r.marshalJSON(layout.Config)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal approval layout config", err)
	}

	qr := query.Insert(constants.TableApprovalPageLayout, map[string]interface{}{
		constants.FieldID:                                    layout.ID,
		constants.FieldObjectAPIName:                         layout.ObjectAPIName,
		constants.FieldSysApprovalPageLayout_LayoutName:      layout.LayoutName,
		constants.FieldSysApprovalPageLayout_Type:            layout.Type,
		constants.FieldSysApprovalPageLayout_IsDefault:       layout.IsDefault,
		constants.FieldIsDeleted:                             layout.IsDeleted,
		constants.FieldSysApprovalPageLayout_Config:          configJSON,
		"LayoutVersion":                                      layout.LayoutVersion,
		"IsCustomized":                                       layout.IsCustomized,
		constants.FieldSysApprovalPageLayout_Source:          layout.Source,
		constants.FieldSysApprovalPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldCreatedDate:                           query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:                      query.Raw("CURRENT_TIMESTAMP"),
	}).OnDuplicateKeyUpdate(map[string]interface{}{
		constants.FieldObjectAPIName:                         layout.ObjectAPIName,
		constants.FieldSysApprovalPageLayout_LayoutName:      layout.LayoutName,
		constants.FieldSysApprovalPageLayout_Type:            layout.Type,
		constants.FieldSysApprovalPageLayout_IsDefault:       layout.IsDefault,
		constants.FieldIsDeleted:                             layout.IsDeleted,
		constants.FieldSysApprovalPageLayout_Config:          configJSON,
		"LayoutVersion":                                      layout.LayoutVersion,
		"IsCustomized":                                       layout.IsCustomized,
		constants.FieldSysApprovalPageLayout_Source:          layout.Source,
		constants.FieldSysApprovalPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldLastModifiedDate:                      query.Raw("CURRENT_TIMESTAMP"),
	}).Build()

	_, err = r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// DeleteApprovalLayout soft-deletes an approval layout by setting IsDeleted = true
func (r *MetadataRepository) DeleteApprovalLayout(ctx context.Context, layoutID string) error {
	sql := fmt.Sprintf("UPDATE `%s` SET `%s` = true, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TableApprovalPageLayout,
		constants.FieldIsDeleted,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	_, err := r.db.ExecContext(ctx, sql, layoutID)
	return err
}

// SetDefaultApprovalLayout clears isDefault on all approval layouts for the object, then sets it on the given layout.
func (r *MetadataRepository) SetDefaultApprovalLayout(ctx context.Context, objectAPIName string, layoutID string) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() //nolint:errcheck // rollback is a no-op after commit

	clearSQL := fmt.Sprintf("UPDATE `%s` SET `%s` = false, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TableApprovalPageLayout,
		constants.FieldSysApprovalPageLayout_IsDefault,
		constants.FieldLastModifiedDate,
		constants.FieldObjectAPIName,
	)
	if _, err := tx.ExecContext(ctx, clearSQL, objectAPIName); err != nil {
		return fmt.Errorf("failed to clear default approval layout: %w", err)
	}

	setSQL := fmt.Sprintf("UPDATE `%s` SET `%s` = true, `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TableApprovalPageLayout,
		constants.FieldSysApprovalPageLayout_IsDefault,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)
	if _, err := tx.ExecContext(ctx, setSQL, layoutID); err != nil {
		return fmt.Errorf("failed to set default approval layout: %w", err)
	}

	return tx.Commit()
}

// UpsertApprovalLayout updates or inserts an approval page layout
func (r *MetadataRepository) UpsertApprovalLayout(ctx context.Context, layout *models.ApprovalPageLayout) error {
	configJSON, err := r.marshalJSON(layout.Config)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal approval layout config", err)
	}

	qr := query.Insert(constants.TableApprovalPageLayout, map[string]interface{}{
		constants.FieldID:                                    layout.ID,
		constants.FieldObjectAPIName:                         layout.ObjectAPIName,
		constants.FieldSysApprovalPageLayout_LayoutName:      layout.LayoutName,
		constants.FieldSysApprovalPageLayout_Type:            layout.Type,
		constants.FieldSysApprovalPageLayout_IsDefault:       layout.IsDefault,
		constants.FieldIsDeleted:                             layout.IsDeleted,
		constants.FieldSysApprovalPageLayout_Config:          configJSON,
		"LayoutVersion":                                      layout.LayoutVersion,
		"IsCustomized":                                       layout.IsCustomized,
		constants.FieldSysApprovalPageLayout_Source:          layout.Source,
		constants.FieldSysApprovalPageLayout_GenerationVersion: layout.GenerationVersion,
		constants.FieldCreatedDate:                           query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:                      query.Raw("CURRENT_TIMESTAMP"),
	}).
		OnDuplicateKeyUpdate(map[string]interface{}{
			constants.FieldObjectAPIName:                         layout.ObjectAPIName,
			constants.FieldSysApprovalPageLayout_LayoutName:      layout.LayoutName,
			constants.FieldSysApprovalPageLayout_Type:            layout.Type,
			constants.FieldSysApprovalPageLayout_IsDefault:       layout.IsDefault,
			constants.FieldIsDeleted:                             layout.IsDeleted,
			constants.FieldSysApprovalPageLayout_Config:          configJSON,
			"LayoutVersion":                                      layout.LayoutVersion,
			"IsCustomized":                                       layout.IsCustomized,
			constants.FieldSysApprovalPageLayout_Source:          layout.Source,
			constants.FieldSysApprovalPageLayout_GenerationVersion: layout.GenerationVersion,
			constants.FieldLastModifiedDate:                      query.Raw("NOW()"),
		}).
		Build()

	_, err = r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// BatchUpsertApprovalLayouts updates or inserts multiple approval page layouts
func (r *MetadataRepository) BatchUpsertApprovalLayouts(ctx context.Context, layouts []*models.ApprovalPageLayout) error {
	for _, layout := range layouts {
		if err := r.UpsertApprovalLayout(ctx, layout); err != nil {
			return err
		}
	}
	return nil
}