package repository

import (
	"fmt"
	"netidhub-saas-be/app/database"
	"netidhub-saas-be/app/database/entity"
	"netidhub-saas-be/app/module/approval_workflow_steps/request"
	"netidhub-saas-be/utils/paginator"

	"github.com/google/uuid"
	"github.com/rs/zerolog"
)

type approvalWorkflowStepsRepository struct {
	DB  *database.Database
	Log zerolog.Logger
}

// ApprovalWorkflowStepsRepository define interface of IApprovalWorkflowStepsRepository
type ApprovalWorkflowStepsRepository interface {
	// Basic CRUD
	GetAll(clientId *uuid.UUID, req request.GetApprovalWorkflowStepsRequest) (steps []*entity.ApprovalWorkflowSteps, paging paginator.Pagination, err error)
	FindOne(clientId *uuid.UUID, id uint) (step *entity.ApprovalWorkflowSteps, err error)
	Create(clientId *uuid.UUID, step *entity.ApprovalWorkflowSteps) (stepReturn *entity.ApprovalWorkflowSteps, err error)
	Update(id uint, step *entity.ApprovalWorkflowSteps) (err error)
	Delete(clientId *uuid.UUID, id uint) (err error)

	// Workflow-specific methods
	GetByWorkflowId(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error)
	GetActiveByWorkflowId(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error)
	FindByWorkflowAndStep(clientId *uuid.UUID, workflowId uint, stepOrder int) (step *entity.ApprovalWorkflowSteps, err error)
	GetNextStep(clientId *uuid.UUID, workflowId uint, currentStep int) (step *entity.ApprovalWorkflowSteps, err error)
	GetPreviousStep(clientId *uuid.UUID, workflowId uint, currentStep int) (step *entity.ApprovalWorkflowSteps, err error)

	// Step management methods
	ReorderSteps(clientId *uuid.UUID, workflowId uint, stepOrders []map[string]interface{}) (err error)
	GetMaxStepOrder(clientId *uuid.UUID, workflowId uint) (maxOrder int, err error)
	GetStepsByUserLevel(clientId *uuid.UUID, userLevelId uint) (steps []*entity.ApprovalWorkflowSteps, err error)

	// Validation methods
	ValidateStepSequence(clientId *uuid.UUID, workflowId uint) (isValid bool, errors []string, err error)
	CheckStepDependencies(clientId *uuid.UUID, stepId uint) (canDelete bool, dependencies []string, err error)

	// Multi-branch support methods
	GetNextSteps(clientId *uuid.UUID, workflowId uint, currentStep int) (steps []*entity.ApprovalWorkflowSteps, err error)
	GetStepsByBranch(clientId *uuid.UUID, workflowId uint, branchName string) (steps []*entity.ApprovalWorkflowSteps, err error)
	GetChildSteps(clientId *uuid.UUID, parentStepId uint) (steps []*entity.ApprovalWorkflowSteps, err error)
	GetRootSteps(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error)
	GetStepsByCondition(clientId *uuid.UUID, workflowId uint, conditionType string, submitterLevelId uint) (steps []*entity.ApprovalWorkflowSteps, err error)
}

func NewApprovalWorkflowStepsRepository(db *database.Database, log zerolog.Logger) ApprovalWorkflowStepsRepository {
	return &approvalWorkflowStepsRepository{
		DB:  db,
		Log: log,
	}
}

// Basic CRUD implementations
func (_i *approvalWorkflowStepsRepository) GetAll(clientId *uuid.UUID, req request.GetApprovalWorkflowStepsRequest) (steps []*entity.ApprovalWorkflowSteps, paging paginator.Pagination, err error) {
	var count int64

	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	// Apply filters from request
	if req.WorkflowID != nil && *req.WorkflowID > 0 {
		query = query.Where("workflow_id = ?", *req.WorkflowID)
	}

	if req.IsActive != nil {
		query = query.Where("is_active = ?", *req.IsActive)
	}

	if req.UserLevelID != nil && *req.UserLevelID > 0 {
		query = query.Where("required_user_level_id = ?", *req.UserLevelID)
	}

	if req.StepOrder != nil && *req.StepOrder > 0 {
		query = query.Where("step_order = ?", *req.StepOrder)
	}

	if req.StepName != nil && *req.StepName != "" {
		query = query.Where("step_name ILIKE ?", "%"+*req.StepName+"%")
	}

	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("workflow_id ASC, step_order ASC")

	err = query.Count(&count).Error
	if err != nil {
		return nil, paginator.Pagination{}, err
	}

	// Apply pagination
	page := req.Page
	limit := req.Limit
	if page <= 0 {
		page = 1
	}
	if limit <= 0 {
		limit = 10
	}

	offset := (page - 1) * limit
	err = query.Offset(offset).Limit(limit).Find(&steps).Error
	if err != nil {
		return nil, paginator.Pagination{}, err
	}

	paging = paginator.Pagination{
		Page:      page,
		Limit:     limit,
		Count:     count,
		TotalPage: int((count + int64(limit) - 1) / int64(limit)),
	}

	return steps, paging, nil
}

func (_i *approvalWorkflowStepsRepository) FindOne(clientId *uuid.UUID, id uint) (step *entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Preload("Workflow").Preload("RequiredUserLevel")

	err = query.First(&step, id).Error
	return step, err
}

func (_i *approvalWorkflowStepsRepository) Create(clientId *uuid.UUID, step *entity.ApprovalWorkflowSteps) (stepReturn *entity.ApprovalWorkflowSteps, err error) {
	step.ClientId = clientId
	err = _i.DB.DB.Create(&step).Error
	return step, err
}

func (_i *approvalWorkflowStepsRepository) Update(id uint, step *entity.ApprovalWorkflowSteps) (err error) {
	err = _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{}).Where("id = ?", id).Updates(step).Error
	return err
}

func (_i *approvalWorkflowStepsRepository) Delete(clientId *uuid.UUID, id uint) (err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	err = query.Delete(&entity.ApprovalWorkflowSteps{}, id).Error
	return err
}

// Workflow-specific methods
func (_i *approvalWorkflowStepsRepository) GetByWorkflowId(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ?", workflowId)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("step_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) GetActiveByWorkflowId(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND is_active = ?", workflowId, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("step_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) FindByWorkflowAndStep(clientId *uuid.UUID, workflowId uint, stepOrder int) (step *entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND step_order = ?", workflowId, stepOrder)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")

	err = query.First(&step).Error
	return step, err
}

func (_i *approvalWorkflowStepsRepository) GetNextStep(clientId *uuid.UUID, workflowId uint, currentStep int) (step *entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND step_order > ? AND is_active = ?", workflowId, currentStep, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("step_order ASC")

	err = query.First(&step).Error
	return step, err
}

func (_i *approvalWorkflowStepsRepository) GetPreviousStep(clientId *uuid.UUID, workflowId uint, currentStep int) (step *entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND step_order < ? AND is_active = ?", workflowId, currentStep, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("step_order DESC")

	err = query.First(&step).Error
	return step, err
}

// Step management methods
func (_i *approvalWorkflowStepsRepository) ReorderSteps(clientId *uuid.UUID, workflowId uint, stepOrders []map[string]interface{}) (err error) {
	// Start transaction
	tx := _i.DB.DB.Begin()
	defer func() {
		if r := recover(); r != nil {
			tx.Rollback()
		}
	}()

	if err := tx.Error; err != nil {
		return err
	}

	// Update each step order
	for _, stepOrder := range stepOrders {
		stepId := stepOrder["id"]
		newOrder := stepOrder["step_order"]

		query := tx.Model(&entity.ApprovalWorkflowSteps{})
		if clientId != nil {
			query = query.Where("client_id = ?", clientId)
		}

		err = query.Where("id = ? AND workflow_id = ?", stepId, workflowId).Update("step_order", newOrder).Error
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	return tx.Commit().Error
}

func (_i *approvalWorkflowStepsRepository) GetMaxStepOrder(clientId *uuid.UUID, workflowId uint) (maxOrder int, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ?")
	query = query.Select("COALESCE(MAX(step_order), 0) as max_order")

	err = query.Scan(&maxOrder).Error
	return maxOrder, err
}

func (_i *approvalWorkflowStepsRepository) GetStepsByUserLevel(clientId *uuid.UUID, userLevelId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("required_user_level_id = ? AND is_active = ?", userLevelId, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel")
	query = query.Order("workflow_id ASC, step_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

// Validation methods
func (_i *approvalWorkflowStepsRepository) ValidateStepSequence(clientId *uuid.UUID, workflowId uint) (isValid bool, errors []string, err error) {
	errors = make([]string, 0)

	// Get all steps for the workflow
	steps, err := _i.GetActiveByWorkflowId(clientId, workflowId)
	if err != nil {
		return false, errors, err
	}

	if len(steps) == 0 {
		errors = append(errors, "Workflow must have at least one step")
		return false, errors, nil
	}

	// Check for sequential step orders starting from 1
	expectedOrder := 1
	for _, step := range steps {
		if step.StepOrder != expectedOrder {
			errors = append(errors, fmt.Sprintf("Step order %d is missing or out of sequence", expectedOrder))
		}
		expectedOrder++
	}

	// Check for duplicate step orders
	stepOrderMap := make(map[int]bool)
	for _, step := range steps {
		if stepOrderMap[step.StepOrder] {
			errors = append(errors, fmt.Sprintf("Duplicate step order found: %d", step.StepOrder))
		}
		stepOrderMap[step.StepOrder] = true
	}

	isValid = len(errors) == 0
	return isValid, errors, nil
}

func (_i *approvalWorkflowStepsRepository) CheckStepDependencies(clientId *uuid.UUID, stepId uint) (canDelete bool, dependencies []string, err error) {
	dependencies = make([]string, 0)

	// Check if step is referenced in any active approval flows
	var activeFlowCount int64
	query := _i.DB.DB.Model(&entity.ArticleApprovalFlows{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	// Join with workflow steps to check current step
	query = query.Joins("JOIN approval_workflow_steps ON article_approval_flows.workflow_id = approval_workflow_steps.workflow_id AND article_approval_flows.current_step = approval_workflow_steps.step_order")
	query = query.Where("approval_workflow_steps.id = ? AND article_approval_flows.status_id IN (1, 4)", stepId) // pending or revision_requested

	err = query.Count(&activeFlowCount).Error
	if err != nil {
		return false, dependencies, err
	}

	if activeFlowCount > 0 {
		dependencies = append(dependencies, fmt.Sprintf("%d active approval flows are currently at this step", activeFlowCount))
	}

	// Check if step is referenced in approval step logs
	var logCount int64
	logQuery := _i.DB.DB.Model(&entity.ArticleApprovalStepLogs{})

	if clientId != nil {
		logQuery = logQuery.Where("client_id = ?", clientId)
	}

	// This is a simplified check - in reality, you might want to join with the step to check step_order
	logQuery = logQuery.Where("step_order IN (SELECT step_order FROM approval_workflow_steps WHERE id = ?)", stepId)

	err = logQuery.Count(&logCount).Error
	if err != nil {
		return false, dependencies, err
	}

	if logCount > 0 {
		dependencies = append(dependencies, fmt.Sprintf("%d approval step logs reference this step", logCount))
	}

	canDelete = len(dependencies) == 0
	return canDelete, dependencies, nil
}

// Multi-branch support methods implementation
func (_i *approvalWorkflowStepsRepository) GetNextSteps(clientId *uuid.UUID, workflowId uint, currentStep int) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	// Get all possible next steps (including parallel branches)
	query = query.Where("workflow_id = ? AND step_order > ? AND is_active = ?", workflowId, currentStep, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel").Preload("ParentStep")
	query = query.Order("step_order ASC, branch_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) GetStepsByBranch(clientId *uuid.UUID, workflowId uint, branchName string) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND branch_name = ? AND is_active = ?", workflowId, branchName, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel").Preload("ParentStep")
	query = query.Order("step_order ASC, branch_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) GetChildSteps(clientId *uuid.UUID, parentStepId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("parent_step_id = ? AND is_active = ?", parentStepId, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel").Preload("ParentStep")
	query = query.Order("branch_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) GetRootSteps(clientId *uuid.UUID, workflowId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	// Get root steps (steps with no parent or step_order = 1)
	query = query.Where("workflow_id = ? AND (parent_step_id IS NULL OR step_order = 1) AND is_active = ?", workflowId, true)
	query = query.Preload("Workflow").Preload("RequiredUserLevel").Preload("ParentStep")
	query = query.Order("step_order ASC, branch_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}

func (_i *approvalWorkflowStepsRepository) GetStepsByCondition(clientId *uuid.UUID, workflowId uint, conditionType string, submitterLevelId uint) (steps []*entity.ApprovalWorkflowSteps, err error) {
	query := _i.DB.DB.Model(&entity.ApprovalWorkflowSteps{})

	if clientId != nil {
		query = query.Where("client_id = ?", clientId)
	}

	query = query.Where("workflow_id = ? AND is_active = ?", workflowId, true)

	// Apply condition filtering
	switch conditionType {
	case "user_level":
		// Steps that apply to specific user levels
		query = query.Where("condition_type = ? OR condition_type IS NULL", conditionType)
	case "user_level_hierarchy":
		// Steps that apply based on user level hierarchy
		query = query.Where("condition_type = ? OR condition_type IS NULL", conditionType)
	case "always":
		// Steps that always apply
		query = query.Where("condition_type = ? OR condition_type IS NULL", conditionType)
	default:
		// Default: get all steps
	}

	query = query.Preload("Workflow").Preload("RequiredUserLevel").Preload("ParentStep")
	query = query.Order("step_order ASC, branch_order ASC")

	err = query.Find(&steps).Error
	return steps, err
}
