package automation

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

	"github.com/nexuscrm/shared/pkg/constants"
)

// This file consumes the package-level flowSourceStandard const (declared in
// executor_flow_version_helpers.go, value "standard" — mirrors the former
// bootstrap.FlowSourceStandard) so the automation feature root does not import
// internal/bootstrap for this literal.

// ProtectedFlowCatalog owns the platform read path for host-owned Flows that
// must not be exposed as public invocable Flows. API dialects consume it through
// contract.FlowCatalog instead of reading _System_StandardAction / Flow tables.
type ProtectedFlowCatalog struct {
	db *sql.DB
}

func NewProtectedFlowCatalog(db *sql.DB) *ProtectedFlowCatalog {
	return &ProtectedFlowCatalog{db: db}
}

func (c *ProtectedFlowCatalog) ProtectedFlowNames(ctx context.Context) ([]string, error) {
	if c == nil || c.db == nil {
		return nil, fmt.Errorf("protected flow catalog database is not configured")
	}

	names := map[string]string{}
	if err := c.addStandardActionFlowNames(ctx, names); err != nil {
		return nil, err
	}
	if err := c.addPackOwnedStandardFlowNames(ctx, names); err != nil {
		return nil, err
	}

	out := make([]string, 0, len(names))
	for _, name := range names {
		out = append(out, name)
	}
	return out, nil
}

func (c *ProtectedFlowCatalog) addStandardActionFlowNames(ctx context.Context, names map[string]string) error {
	query := fmt.Sprintf(
		`SELECT %s
		   FROM %s
		  WHERE %s IS NOT NULL AND %s <> ''
		    AND (%s = 0 OR %s IS NULL)
		    AND (%s = 0 OR %s IS NULL)`,
		constants.FieldSysStandardAction_FlowDeveloperName,
		constants.TableStandardAction,
		constants.FieldSysStandardAction_FlowDeveloperName,
		constants.FieldSysStandardAction_FlowDeveloperName,
		constants.FieldSysStandardAction_IsDeleted,
		constants.FieldSysStandardAction_IsDeleted,
		constants.FieldSysStandardAction_IsCustomized,
		constants.FieldSysStandardAction_IsCustomized,
	)
	rows, err := c.db.QueryContext(ctx, query)
	if err != nil {
		return fmt.Errorf("load standard action flow names: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var flowName sql.NullString
		if err := rows.Scan(&flowName); err != nil {
			return fmt.Errorf("scan standard action flow name: %w", err)
		}
		addProtectedFlowName(names, flowName.String)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate standard action flow names: %w", err)
	}
	return nil
}

func (c *ProtectedFlowCatalog) addPackOwnedStandardFlowNames(ctx context.Context, names map[string]string) error {
	query := fmt.Sprintf(
		`SELECT fd.%s
		   FROM %s fd
		   JOIN %s f ON f.%s = fd.%s AND f.%s = 0
		  WHERE fd.%s IS NOT NULL AND fd.%s <> ''
		    AND fd.%s = 0
		    AND fd.%s = 0
		    AND f.%s = ?`,
		constants.FieldFlowDefinition_DeveloperName,
		constants.TableFlowDefinition,
		constants.TableFlow,
		constants.FieldFlow_ID,
		constants.FieldFlowDefinition_ActiveVersionID,
		constants.FieldFlow_IsDeleted,
		constants.FieldFlowDefinition_DeveloperName,
		constants.FieldFlowDefinition_DeveloperName,
		constants.FieldFlowDefinition_IsDeleted,
		constants.FieldFlowDefinition_IsCustomized,
		constants.FieldFlow_Source,
	)
	rows, err := c.db.QueryContext(ctx, query, flowSourceStandard)
	if err != nil {
		return fmt.Errorf("load pack-owned standard flow names: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var flowName sql.NullString
		if err := rows.Scan(&flowName); err != nil {
			return fmt.Errorf("scan pack-owned standard flow name: %w", err)
		}
		addProtectedFlowName(names, flowName.String)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate pack-owned standard flow names: %w", err)
	}
	return nil
}

func addProtectedFlowName(names map[string]string, flowName string) {
	trimmed := strings.TrimSpace(flowName)
	if key := strings.ToLower(trimmed); key != "" {
		if _, exists := names[key]; !exists {
			names[key] = trimmed
		}
	}
}