package packs

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

	"github.com/nexuscrm/automation/triggersub"
)

type standardFlowPackSweepRow struct {
	ID              string
	DeveloperName   string
	ActiveVersionID sql.NullString
}

func newSourceBackedStandardFlowPackSweeperComponent(packName string, declaredDeveloperNames []string) PackComponent {
	manifestComponent := ManifestComponent{
		Kind:          ComponentKindStandardFlow,
		BusinessKey:   "_pack_sweep",
		Manageability: ManageabilityLocked,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: manifestComponent,
		applyGroupKey:     string(ComponentKindStandardFlow) + "_pack_sweep",
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applySourceBackedStandardFlowPackSweep(ctx, deps.DB, packName, declaredDeveloperNames)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifySourceBackedStandardFlowPackSweeper(ctx, deps.DB, packName, declaredDeveloperNames)
		},
	}
}

func applySourceBackedStandardFlowPackSweep(ctx context.Context, db *sql.DB, packName string, declaredDeveloperNames []string) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin standard_flow pack sweep for %s: %w", strings.TrimSpace(packName), err)
	}
	defer tx.Rollback()

	stale, err := loadUndeclaredStandardFlowRows(ctx, tx, packName, declaredDeveloperNames, true)
	if err != nil {
		return err
	}
	for _, row := range stale {
		result, err := tx.ExecContext(ctx, `
UPDATE FlowDefinition
   SET IsDeleted = 1, ActiveVersionId = NULL, LastModifiedDate = NOW()
 WHERE Id = ?
   AND COALESCE(OwnerPack, '') = ?
   AND COALESCE(IsDeleted, 0) = 0
   AND COALESCE(IsCustomized, 0) = 0
   AND (
       ActiveVersionId IS NULL
       OR EXISTS (
           SELECT 1
             FROM Flow
            WHERE Flow.Id = FlowDefinition.ActiveVersionId
              AND Flow.DefinitionId = FlowDefinition.Id
              AND COALESCE(Flow.IsDeleted, 0) = 0
              AND Flow.Status = 'Active'
              AND Flow.Source = ?
       )
   )`, row.ID, strings.TrimSpace(packName), FlowSourceStandard)
		if err != nil {
			return fmt.Errorf("retire undeclared standard_flow %s for pack %s: %w", row.DeveloperName, strings.TrimSpace(packName), err)
		}
		updated, err := result.RowsAffected()
		if err != nil {
			return fmt.Errorf("read retired standard_flow count for %s in pack %s: %w", row.DeveloperName, strings.TrimSpace(packName), err)
		}
		if updated != 1 {
			return fmt.Errorf("retire undeclared standard_flow %s for pack %s: definition changed after ownership validation", row.DeveloperName, strings.TrimSpace(packName))
		}
		if err := triggersub.Disable(ctx, tx, row.ID); err != nil {
			return fmt.Errorf("disable trigger subscriptions for retired standard_flow %s in pack %s: %w", row.DeveloperName, strings.TrimSpace(packName), err)
		}
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit standard_flow pack sweep for %s: %w", strings.TrimSpace(packName), err)
	}
	return nil
}

func verifySourceBackedStandardFlowPackSweeper(ctx context.Context, db *sql.DB, packName string, declaredDeveloperNames []string) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}
	stale, err := loadUndeclaredStandardFlowRows(ctx, db, packName, declaredDeveloperNames, false)
	if err != nil {
		return err
	}
	if len(stale) == 0 {
		return nil
	}
	names := make([]string, 0, len(stale))
	for _, row := range stale {
		names = append(names, row.DeveloperName)
	}
	sort.Strings(names)
	return fmt.Errorf("pack %s has %d undeclared standard_flow row(s) still live: %s", strings.TrimSpace(packName), len(names), strings.Join(names, ", "))
}

type standardFlowPackSweepQueryer interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
}

func loadUndeclaredStandardFlowRows(ctx context.Context, db standardFlowPackSweepQueryer, packName string, declaredDeveloperNames []string, lock bool) ([]standardFlowPackSweepRow, error) {
	declared := make(map[string]struct{}, len(declaredDeveloperNames))
	for _, developerName := range declaredDeveloperNames {
		developerName = strings.ToLower(strings.TrimSpace(developerName))
		if developerName != "" {
			declared[developerName] = struct{}{}
		}
	}
	query := `
SELECT fd.Id, fd.DeveloperName, fd.ActiveVersionId,
       active_version.Id, active_version.DefinitionId, active_version.Source,
       active_version.Status, COALESCE(active_version.IsDeleted, 0)
  FROM FlowDefinition fd
  LEFT JOIN Flow active_version ON active_version.Id = fd.ActiveVersionId
 WHERE COALESCE(fd.OwnerPack, '') = ?
   AND COALESCE(fd.IsDeleted, 0) = 0
   AND COALESCE(fd.IsCustomized, 0) = 0
 ORDER BY fd.DeveloperName, fd.Id`
	if lock {
		query += " FOR UPDATE"
	}
	rows, err := db.QueryContext(ctx, query, strings.TrimSpace(packName))
	if err != nil {
		return nil, fmt.Errorf("query live standard_flow rows for pack sweep %s: %w", strings.TrimSpace(packName), err)
	}
	defer rows.Close()

	var stale []standardFlowPackSweepRow
	for rows.Next() {
		var row standardFlowPackSweepRow
		var activeID, activeDefinitionID, activeSource, activeStatus sql.NullString
		var activeIsDeleted int
		if err := rows.Scan(
			&row.ID, &row.DeveloperName, &row.ActiveVersionID,
			&activeID, &activeDefinitionID, &activeSource, &activeStatus, &activeIsDeleted,
		); err != nil {
			return nil, fmt.Errorf("scan standard_flow row for pack sweep %s: %w", strings.TrimSpace(packName), err)
		}
		if _, ok := declared[strings.ToLower(strings.TrimSpace(row.DeveloperName))]; ok {
			continue
		}
		if row.ActiveVersionID.Valid {
			switch {
			case !activeID.Valid:
				return nil, fmt.Errorf("undeclared standard_flow %s for pack %s has missing active Flow %s", row.DeveloperName, strings.TrimSpace(packName), row.ActiveVersionID.String)
			case !activeDefinitionID.Valid || activeDefinitionID.String != row.ID:
				return nil, fmt.Errorf("undeclared standard_flow %s for pack %s has active Flow %s owned by definition %q", row.DeveloperName, strings.TrimSpace(packName), row.ActiveVersionID.String, activeDefinitionID.String)
			case activeIsDeleted != 0:
				return nil, fmt.Errorf("undeclared standard_flow %s for pack %s points to deleted active Flow %s", row.DeveloperName, strings.TrimSpace(packName), row.ActiveVersionID.String)
			case !activeStatus.Valid || activeStatus.String != "Active":
				return nil, fmt.Errorf("undeclared standard_flow %s for pack %s points to non-active Flow %s with status %q", row.DeveloperName, strings.TrimSpace(packName), row.ActiveVersionID.String, activeStatus.String)
			case activeSource.String == FlowSourceCustom:
				continue
			case !activeSource.Valid || activeSource.String != FlowSourceStandard:
				return nil, fmt.Errorf("undeclared standard_flow %s for pack %s has active Flow %s with unsupported source %q", row.DeveloperName, strings.TrimSpace(packName), row.ActiveVersionID.String, activeSource.String)
			}
		}
		stale = append(stale, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate standard_flow rows for pack sweep %s: %w", strings.TrimSpace(packName), err)
	}
	return stale, nil
}