package packs

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

type emailTemplatePackBatch struct {
	declaredDeveloperNames []string
}

func newEmailTemplatePackSweeperComponent(packName string, batch *emailTemplatePackBatch) PackComponent {
	manifestComponent := ManifestComponent{
		Kind:          ComponentKindEmailTemplate,
		BusinessKey:   JoinComponentBusinessKey(packName, "@sweeper"),
		Manageability: ManageabilityLocked,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: manifestComponent,
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applyEmailTemplatePackSweeper(ctx, deps.DB, batch, packName)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifyEmailTemplatePackSweeper(ctx, deps.DB, batch, packName)
		},
	}
}

func applyEmailTemplatePackSweeper(ctx context.Context, db *sql.DB, batch *emailTemplatePackBatch, ownerPack string) error {
	orphans, err := queryOrphanEmailTemplateRows(ctx, db, batch, ownerPack)
	if err != nil {
		return err
	}
	for _, orphanID := range orphans {
		if _, err := db.ExecContext(ctx, `
UPDATE _System_EmailTemplate
SET IsActive = 0, IsDeleted = 1, LastModifiedDate = NOW()
WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0`, orphanID); err != nil {
			return fmt.Errorf("soft-delete orphan email_template %s for pack %s: %w", orphanID, ownerPack, err)
		}
	}
	return nil
}

func verifyEmailTemplatePackSweeper(ctx context.Context, db *sql.DB, batch *emailTemplatePackBatch, ownerPack string) error {
	orphans, err := queryOrphanEmailTemplateRows(ctx, db, batch, ownerPack)
	if err != nil {
		return err
	}
	if len(orphans) == 0 {
		return nil
	}
	sort.Strings(orphans)
	return fmt.Errorf("pack %s has %d undeclared email_template row(s) still live: %s", ownerPack, len(orphans), strings.Join(orphans, ", "))
}

func queryOrphanEmailTemplateRows(ctx context.Context, db *sql.DB, batch *emailTemplatePackBatch, ownerPack string) ([]string, error) {
	if db == nil {
		return nil, fmt.Errorf("database is required")
	}
	declaredSet := map[string]struct{}{}
	if batch != nil {
		for _, developerName := range batch.declaredDeveloperNames {
			key := strings.ToLower(strings.TrimSpace(developerName))
			if key != "" {
				declaredSet[key] = struct{}{}
			}
		}
	}
	rows, err := db.QueryContext(ctx, `
SELECT Id, DeveloperName
FROM _System_EmailTemplate
WHERE COALESCE(OwnerPack, '') = ? AND COALESCE(IsDeleted, 0) = 0`, strings.TrimSpace(ownerPack))
	if err != nil {
		return nil, fmt.Errorf("query live email_template rows for pack sweep %s: %w", ownerPack, err)
	}
	defer rows.Close()

	var orphanIDs []string
	for rows.Next() {
		var id, developerName string
		if err := rows.Scan(&id, &developerName); err != nil {
			return nil, fmt.Errorf("scan email_template row for pack sweep %s: %w", ownerPack, err)
		}
		if _, ok := declaredSet[strings.ToLower(strings.TrimSpace(developerName))]; ok {
			continue
		}
		orphanIDs = append(orphanIDs, id)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate email_template rows for pack sweep %s: %w", ownerPack, err)
	}
	return orphanIDs, nil
}