package persistence

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"time"

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

// --- Action CRUD ---

// GetActions queries actions for an object
func (r *MetadataRepository) GetActions(ctx context.Context, objectAPIName string) ([]*models.ActionMetadata, error) {
	qr := query.From(constants.TableAction).
		Select(constants.ColsSystemAction).
		WhereRaw(fmt.Sprintf("LOWER(`%s`) = LOWER(?)", constants.FieldSysAction_ObjectAPIName), []interface{}{objectAPIName}).
		Build()

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

	actions := make([]*models.ActionMetadata, 0)
	for rows.Next() {
		action, err := r.scanAction(rows)
		if err != nil {
			continue
		}
		actions = append(actions, action)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating actions", err)
	}
	return actions, nil
}

// GetAction queries a single action by ID
func (r *MetadataRepository) GetAction(ctx context.Context, id string) (*models.ActionMetadata, error) {
	qr := query.From(constants.TableAction).
		Select(constants.ColsSystemAction).
		Where(constants.FieldSysAction_ID, id).
		Build()

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

// GetAllActions returns all actions
func (r *MetadataRepository) GetAllActions(ctx context.Context) ([]*models.ActionMetadata, error) {
	qr := query.From(constants.TableAction).
		Select(constants.ColsSystemAction).
		Build()

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

	actions := make([]*models.ActionMetadata, 0)
	for rows.Next() {
		action, err := r.scanAction(rows)
		if err != nil {
			log.Printf("Warning: Failed to scan action: %v", err)
			continue
		}
		actions = append(actions, action)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating all actions", err)
	}
	return actions, nil
}

// CreateAction creates a new action
func (r *MetadataRepository) CreateAction(ctx context.Context, action *models.ActionMetadata) error {
	configJSON, _ := json.Marshal(action.Config)

	values := map[string]interface{}{
		constants.FieldSysAction_ID:            action.ID,
		constants.FieldSysAction_ObjectAPIName: action.ObjectAPIName,
		constants.FieldSysAction_Name:          action.Name,
		constants.FieldSysAction_Label:         action.Label,
		constants.FieldSysAction_Type:          action.Type,
		constants.FieldSysAction_Icon:          action.Icon,
		constants.FieldSysAction_TargetObject:  action.TargetObject,
		constants.FieldSysAction_Config:        string(configJSON),
		constants.FieldCreatedDate:             time.Now(),
		constants.FieldLastModifiedDate:        time.Now(),
	}

	qr := query.Insert(constants.TableAction, values).Build()
	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return apperrors.NewInternalError("failed to create action", err)
	}
	return nil
}

// UpdateAction updates an existing action
func (r *MetadataRepository) UpdateAction(ctx context.Context, actionID string, updates *models.ActionMetadata) error {
	configJSON, _ := json.Marshal(updates.Config)

	values := map[string]interface{}{
		constants.FieldSysAction_Name:         updates.Name,
		constants.FieldSysAction_Label:        updates.Label,
		constants.FieldSysAction_Type:         updates.Type,
		constants.FieldSysAction_Icon:         updates.Icon,
		constants.FieldSysAction_TargetObject: updates.TargetObject,
		constants.FieldSysAction_Config:       string(configJSON),
		constants.FieldLastModifiedDate:       time.Now(),
	}

	qr := query.Update(constants.TableAction).
		Set(values).
		Where(constants.FieldSysAction_ID, actionID).
		Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return apperrors.NewInternalError("failed to update action", err)
	}
	return nil
}

// DeleteAction deletes an action
func (r *MetadataRepository) DeleteAction(ctx context.Context, actionID string) error {
	qr := query.Delete(constants.TableAction).
		Where(constants.FieldSysAction_ID, actionID).
		Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return apperrors.NewInternalError("failed to delete action", err)
	}
	return nil
}

// CheckActionExists checks if an action with the given name exists for the object
func (r *MetadataRepository) CheckActionExists(ctx context.Context, objectAPIName, name string) (bool, error) {
	qr := query.From(constants.TableAction).
		SkipID().
		AddSelectRaw("COUNT(*)").
		WhereRaw(fmt.Sprintf("LOWER(`%s`) = LOWER(?) AND LOWER(`%s`) = LOWER(?)",
			constants.FieldSysAction_ObjectAPIName, constants.FieldSysAction_Name),
			[]interface{}{objectAPIName, name}).
		Build()

	var count int
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&count)
	if err != nil {
		return false, apperrors.NewInternalError("failed to check action existence", err)
	}
	return count > 0, nil
}