package modules

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"net/http"
	"strconv"
	"strings"
	"time"

	"gorm.io/gorm"

	"security-center-backend/internal/config"
	coremodel "security-center-backend/internal/model"
	caasmmodel "security-center-backend/internal/modules/caasm/model"
	scanclient "security-center-backend/internal/modules/scan/client"
	scanhandler "security-center-backend/internal/modules/scan/handler"
	scanmodel "security-center-backend/internal/modules/scan/model"
	scanrepository "security-center-backend/internal/modules/scan/repository"
	scanservice "security-center-backend/internal/modules/scan/service"
	"security-center-backend/internal/pkg/logger"
)

type ScanModule struct {
	Handler   *scanhandler.Handler
	StopHooks []StopHook
}

type ScanBuildInput struct {
	DB         *gorm.DB
	Repo       scanrepository.ScanRepository
	Config     config.ScannerConfig
	Logger     *logger.Logger
	HTTPClient *http.Client
}

func BuildScan(input ScanBuildInput) *ScanModule {
	if input.DB == nil || input.Repo == nil {
		return &ScanModule{}
	}

	var scanner scanservice.ScannerClient
	if input.Config.Enabled {
		client, err := scanclient.NewHTTPClient(scanclient.Config{
			BaseURL:   input.Config.BaseURL,
			APIToken:  input.Config.APIToken,
			Timeout:   input.Config.Timeout,
			UserAgent: input.Config.UserAgent,
		}, input.HTTPClient)
		if err != nil {
			if input.Logger != nil {
				input.Logger.Warn("scan scanner client disabled by invalid configuration", "error", err)
			}
		} else {
			scanner = client
		}
	}

	repo := &scanRepositoryAdapter{db: input.DB, repo: input.Repo, now: time.Now}
	resolver := &caasmPortTargetResolver{db: input.DB}
	service := scanservice.New(repo, resolver, scanner, scanservice.Config{
		CallbackToken:              input.Config.CallbackToken,
		MaxTargetsPerBatch:         input.Config.MaxTargetsPerBatch,
		MaxCampaignTargets:         input.Config.MaxCampaignTargets,
		CampaignSubmitConcurrency:  input.Config.CampaignSubmitConcurrency,
		CampaignDispatcherInterval: input.Config.CampaignDispatcherInterval,
		AllowNonPublicTargets:      input.Config.AllowNonPublicTargets,
		Now:                        time.Now,
	})
	module := &ScanModule{Handler: scanhandler.New(service)}
	if scanner != nil {
		ctx, cancel := context.WithCancel(context.Background())
		go service.RunCampaignDispatcher(ctx)
		module.StopHooks = append(module.StopHooks, func(context.Context) error {
			cancel()
			return nil
		})
	}
	return module
}

type caasmPortTargetResolver struct {
	db *gorm.DB
}

func (r *caasmPortTargetResolver) ResolveTargets(ctx context.Context, req scanservice.ResolveTargetsRequest) ([]scanservice.TargetCandidate, error) {
	if r == nil || r.db == nil {
		return nil, scanservice.ErrMissingDependency
	}
	if req.Source != scanservice.SourceCAASMPorts {
		return nil, fmt.Errorf("%w: unsupported scan source", scanservice.ErrInvalidRequest)
	}

	switch req.TargetScope {
	case scanservice.TargetScopeSelectedPorts, "":
		return r.resolveSelectedPorts(ctx, req.TargetIDs)
	case scanservice.TargetScopeAllPublicPorts:
		return r.resolveAllPublicPorts(ctx, req.Limit)
	default:
		return nil, fmt.Errorf("%w: unsupported target_scope", scanservice.ErrInvalidRequest)
	}
}

func (r *caasmPortTargetResolver) ResolveTargetPage(ctx context.Context, req scanservice.ResolveTargetPageRequest) (*scanservice.TargetPage, error) {
	if r == nil || r.db == nil {
		return nil, scanservice.ErrMissingDependency
	}
	if req.Source != scanservice.SourceCAASMPorts {
		return nil, fmt.Errorf("%w: unsupported scan source", scanservice.ErrInvalidRequest)
	}
	if req.TargetScope != scanservice.TargetScopeAllPublicPorts {
		return nil, fmt.Errorf("%w: unsupported target_scope", scanservice.ErrInvalidRequest)
	}
	if req.Limit <= 0 {
		return nil, fmt.Errorf("%w: target limit is required", scanservice.ErrInvalidRequest)
	}
	var ports []caasmmodel.IPPortIndex
	if err := r.db.WithContext(ctx).
		Preload("Asset").
		Where("LOWER(COALESCE(exposure, '')) = ?", "public").
		Where("id > ?", req.AfterID).
		Order("id ASC").
		Limit(req.Limit + 1).
		Find(&ports).Error; err != nil {
		return nil, err
	}
	hasMore := len(ports) > req.Limit
	if hasMore {
		ports = ports[:req.Limit]
	}
	out := make([]scanservice.TargetCandidate, 0, len(ports))
	var nextCursor uint64
	for _, port := range ports {
		out = append(out, targetCandidateFromPort(port))
		nextCursor = port.ID
	}
	return &scanservice.TargetPage{
		Items:      out,
		NextCursor: nextCursor,
		HasMore:    hasMore,
	}, nil
}

func (r *caasmPortTargetResolver) resolveSelectedPorts(ctx context.Context, targetIDs []uint64) ([]scanservice.TargetCandidate, error) {
	if len(targetIDs) == 0 {
		return nil, fmt.Errorf("%w: target_ids are required", scanservice.ErrInvalidRequest)
	}
	var ports []caasmmodel.IPPortIndex
	if err := r.db.WithContext(ctx).
		Preload("Asset").
		Where("id IN ?", targetIDs).
		Find(&ports).Error; err != nil {
		return nil, err
	}

	byID := make(map[uint64]caasmmodel.IPPortIndex, len(ports))
	for _, port := range ports {
		byID[port.ID] = port
	}

	out := make([]scanservice.TargetCandidate, 0, len(targetIDs))
	for _, id := range targetIDs {
		port, ok := byID[id]
		if !ok {
			out = append(out, scanservice.TargetCandidate{
				CAASMPortID:  uint64Ptr(id),
				PortSnapshot: map[string]any{"id": id},
			})
			continue
		}
		out = append(out, targetCandidateFromPort(port))
	}
	return out, nil
}

func (r *caasmPortTargetResolver) resolveAllPublicPorts(ctx context.Context, limit int) ([]scanservice.TargetCandidate, error) {
	if limit <= 0 {
		return nil, fmt.Errorf("%w: target limit is required", scanservice.ErrInvalidRequest)
	}
	var ports []caasmmodel.IPPortIndex
	if err := r.db.WithContext(ctx).
		Preload("Asset").
		Where("LOWER(COALESCE(exposure, '')) = ?", "public").
		Order("id ASC").
		Limit(limit).
		Find(&ports).Error; err != nil {
		return nil, err
	}

	out := make([]scanservice.TargetCandidate, 0, len(ports))
	for _, port := range ports {
		out = append(out, targetCandidateFromPort(port))
	}
	return out, nil
}

func targetCandidateFromPort(port caasmmodel.IPPortIndex) scanservice.TargetCandidate {
	asset := port.Asset
	cloudAccountID := ""
	if asset.CloudAccountID != nil {
		cloudAccountID = strconv.FormatUint(*asset.CloudAccountID, 10)
	}
	owner := asset.Service
	if asset.OwnerEmployeeID != nil {
		owner = "employee:" + strconv.FormatUint(*asset.OwnerEmployeeID, 10)
	}
	if owner == "" && asset.DepartmentID != nil {
		owner = "department:" + *asset.DepartmentID
	}
	portID := port.ID
	assetID := port.AssetID
	return scanservice.TargetCandidate{
		CAASMAssetID:   &assetID,
		CAASMPortID:    &portID,
		ScannerAssetID: strconv.FormatUint(assetID, 10),
		PublicIP:       port.IPAddress,
		Port:           int(port.Port),
		Transport:      strings.ToLower(strings.TrimSpace(port.Protocol)),
		CloudProvider:  asset.Provider,
		CloudAccountID: cloudAccountID,
		Region:         asset.Region,
		ResourceType:   string(asset.AssetType),
		ResourceID:     asset.NativeID,
		Owner:          owner,
		Environment:    asset.Environment,
		AssetSnapshot: map[string]any{
			"id":               asset.ID,
			"name":             asset.Name,
			"native_id":        asset.NativeID,
			"asset_type":       asset.AssetType,
			"service":          asset.Service,
			"criticality":      asset.Criticality,
			"status":           asset.Status,
			"provider":         asset.Provider,
			"region":           asset.Region,
			"environment":      asset.Environment,
			"cloud_account_id": cloudAccountID,
		},
		PortSnapshot: map[string]any{
			"id":                 port.ID,
			"ip_address":         port.IPAddress,
			"port":               port.Port,
			"protocol":           port.Protocol,
			"service_name":       port.ServiceName,
			"asset_service":      port.AssetService,
			"product":            port.Product,
			"version":            port.Version,
			"exposure":           port.Exposure,
			"status":             port.Status,
			"observation_source": port.ObservationSource,
			"last_seen_at":       port.LastSeenAt,
		},
		Metadata: map[string]any{
			"asset_name":        asset.Name,
			"asset_service":     firstNonEmpty(port.AssetService, asset.Service),
			"service_name":      port.ServiceName,
			"asset_criticality": asset.Criticality,
			"port_exposure":     port.Exposure,
			"port_status":       port.Status,
		},
	}
}

type scanRepositoryAdapter struct {
	db   *gorm.DB
	repo scanrepository.ScanRepository
	now  func() time.Time
}

func (r *scanRepositoryAdapter) CreateCampaign(ctx context.Context, input scanservice.CreateCampaignRecordInput) (*scanservice.Campaign, error) {
	campaign := &scanmodel.Campaign{
		RequestID:   input.RequestID,
		Source:      input.Source,
		TargetScope: input.TargetScope,
		Status:      scanmodel.CampaignStatusQueued,
		Metadata:    jsonb(input.Metadata),
	}
	if err := r.repo.CreateCampaign(ctx, campaign); err != nil {
		if isDuplicateKey(err) {
			existing, findErr := r.repo.FindCampaignByRequestID(ctx, input.RequestID)
			if findErr == nil && existing != nil {
				return mapCampaign(existing), nil
			}
		}
		return nil, err
	}
	return mapCampaign(campaign), nil
}

func (r *scanRepositoryAdapter) GetCampaign(ctx context.Context, id uint64) (*scanservice.Campaign, error) {
	campaign, err := r.repo.GetCampaign(ctx, id)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, scanservice.ErrUnknownCampaign
	}
	if err != nil {
		return nil, err
	}
	out := mapCampaign(campaign)
	campaigns := []scanservice.Campaign{*out}
	r.enrichCampaignSummaries(ctx, campaigns)
	*out = campaigns[0]
	return out, nil
}

func (r *scanRepositoryAdapter) FindCampaignByRequestID(ctx context.Context, requestID string) (*scanservice.Campaign, error) {
	campaign, err := r.repo.FindCampaignByRequestID(ctx, requestID)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return mapCampaign(campaign), nil
}

func (r *scanRepositoryAdapter) ListCampaigns(ctx context.Context, filter scanservice.ListCampaignsFilter) (*scanservice.PaginatedResult[scanservice.Campaign], error) {
	items, total, err := r.repo.ListCampaigns(ctx, scanrepository.CampaignListFilter{
		Status:    filter.Status,
		Query:     filter.Query,
		CreatedBy: filter.CreatedBy,
		From:      filter.From,
		To:        filter.To,
	}, filter.Page, filter.PageSize)
	if err != nil {
		return nil, err
	}
	out := make([]scanservice.Campaign, 0, len(items))
	for i := range items {
		out = append(out, *mapCampaign(&items[i]))
	}
	r.enrichCampaignSummaries(ctx, out)
	return &scanservice.PaginatedResult[scanservice.Campaign]{Items: out, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) ListCampaignBatches(ctx context.Context, campaignID uint64, filter scanservice.ListBatchesFilter) (*scanservice.PaginatedResult[scanservice.Batch], error) {
	filter.CampaignID = &campaignID
	return r.ListBatches(ctx, filter)
}

func (r *scanRepositoryAdapter) ClaimNextCampaign(ctx context.Context) (*scanservice.Campaign, error) {
	campaign, err := r.repo.ClaimNextCampaign(ctx)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return mapCampaign(campaign), nil
}

func (r *scanRepositoryAdapter) UpdateCampaignFields(ctx context.Context, campaignID uint64, fields scanservice.CampaignUpdateFields) error {
	updates := map[string]interface{}{}
	if fields.Status != nil {
		updates["status"] = scanmodel.CampaignStatus(*fields.Status)
	}
	if fields.TargetCount != nil {
		updates["target_count"] = *fields.TargetCount
	}
	if fields.AcceptedTargets != nil {
		updates["accepted_targets"] = *fields.AcceptedTargets
	}
	if fields.RejectedTargets != nil {
		updates["rejected_targets"] = *fields.RejectedTargets
	}
	if fields.QueuedJobs != nil {
		updates["queued_jobs"] = *fields.QueuedJobs
	}
	if fields.RunningJobs != nil {
		updates["running_jobs"] = *fields.RunningJobs
	}
	if fields.SucceededJobs != nil {
		updates["succeeded_jobs"] = *fields.SucceededJobs
	}
	if fields.FailedJobs != nil {
		updates["failed_jobs"] = *fields.FailedJobs
	}
	if fields.SkippedJobs != nil {
		updates["skipped_jobs"] = *fields.SkippedJobs
	}
	if fields.BatchCount != nil {
		updates["batch_count"] = *fields.BatchCount
	}
	if fields.CompletedBatches != nil {
		updates["completed_batches"] = *fields.CompletedBatches
	}
	if fields.FailedBatches != nil {
		updates["failed_batches"] = *fields.FailedBatches
	}
	if fields.NextCursor != nil {
		updates["next_cursor"] = *fields.NextCursor
	}
	if fields.SubmittedAt != nil {
		updates["submitted_at"] = fields.SubmittedAt
	}
	if fields.CompletedAt != nil {
		updates["completed_at"] = fields.CompletedAt
	}
	if fields.LastSyncedAt != nil {
		updates["last_synced_at"] = fields.LastSyncedAt
	}
	if fields.ClearScannerError {
		updates["scanner_error"] = nil
	} else if fields.ScannerError != nil {
		updates["scanner_error"] = nullableString(*fields.ScannerError)
	}
	return r.repo.UpdateCampaignFields(ctx, campaignID, updates)
}

func (r *scanRepositoryAdapter) UpdateCampaignBatchPartCount(ctx context.Context, campaignID uint64, partCount int) error {
	return r.repo.UpdateCampaignBatchPartCount(ctx, campaignID, partCount)
}

func (r *scanRepositoryAdapter) AggregateCampaign(ctx context.Context, campaignID uint64) (*scanservice.Campaign, error) {
	now := r.clock()
	campaign, err := r.repo.GetCampaign(ctx, campaignID)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, scanservice.ErrUnknownCampaign
	}
	if err != nil {
		return nil, err
	}
	var rows []scanmodel.Batch
	if err := r.db.WithContext(ctx).
		Where("campaign_id = ? AND deleted_at IS NULL", campaignID).
		Find(&rows).Error; err != nil {
		return nil, err
	}
	var targetCount, acceptedTargets, rejectedTargets int
	var queuedJobs, runningJobs, succeededJobs, failedJobs, skippedJobs int
	var completedBatches, failedBatches, canceledBatches int
	var lastSyncedAt *time.Time
	for i := range rows {
		batch := rows[i]
		targetCount += batch.TargetCount
		acceptedTargets += batch.AcceptedTargets
		rejectedTargets += batch.RejectedTargets
		queuedJobs += batch.QueuedJobs
		runningJobs += batch.RunningJobs
		succeededJobs += batch.SucceededJobs
		failedJobs += batch.FailedJobs
		skippedJobs += batch.SkippedJobs
		if batch.Status == scanmodel.BatchStatusCompleted {
			completedBatches++
		}
		if batch.Status == scanmodel.BatchStatusCanceled {
			canceledBatches++
		}
		if batch.Status == scanmodel.BatchStatusFailed || batch.Status == scanmodel.BatchStatusSyncFailed {
			failedBatches++
		}
		if batch.LastSyncedAt != nil && (lastSyncedAt == nil || batch.LastSyncedAt.After(*lastSyncedAt)) {
			lastSyncedAt = batch.LastSyncedAt
		}
	}
	if campaign != nil {
		if campaign.TargetCount > targetCount {
			targetCount = campaign.TargetCount
		}
		if campaign.RejectedTargets > rejectedTargets {
			rejectedTargets = campaign.RejectedTargets
		}
	}
	status := scanmodel.CampaignStatusRunning
	var completedAt *time.Time
	if len(rows) == 0 {
		status = scanmodel.CampaignStatusFailed
		completedAt = &now
	} else if campaign.Status == scanmodel.CampaignStatusCanceled {
		status = scanmodel.CampaignStatusCanceled
		if completedBatches+failedBatches+canceledBatches == len(rows) && runningJobs == 0 && queuedJobs == 0 {
			completedAt = &now
		}
	} else if completedBatches+failedBatches+canceledBatches == len(rows) && runningJobs == 0 && queuedJobs == 0 {
		completedAt = &now
		if failedBatches > 0 {
			status = scanmodel.CampaignStatusFailed
		} else if canceledBatches > 0 {
			status = scanmodel.CampaignStatusCanceled
		} else {
			status = scanmodel.CampaignStatusCompleted
		}
	}
	updates := map[string]interface{}{
		"status":            status,
		"target_count":      targetCount,
		"accepted_targets":  acceptedTargets,
		"rejected_targets":  rejectedTargets,
		"queued_jobs":       queuedJobs,
		"running_jobs":      runningJobs,
		"succeeded_jobs":    succeededJobs,
		"failed_jobs":       failedJobs,
		"skipped_jobs":      skippedJobs,
		"batch_count":       len(rows),
		"completed_batches": completedBatches + canceledBatches,
		"failed_batches":    failedBatches,
	}
	if lastSyncedAt != nil {
		updates["last_synced_at"] = lastSyncedAt
	}
	if completedAt != nil {
		updates["completed_at"] = completedAt
	}
	if err := r.repo.UpdateCampaignFields(ctx, campaignID, updates); err != nil {
		return nil, err
	}
	return r.GetCampaign(ctx, campaignID)
}

func (r *scanRepositoryAdapter) CreateBatch(ctx context.Context, input scanservice.CreateBatchRecordInput) (*scanservice.Batch, error) {
	now := r.clock()
	batch := &scanmodel.Batch{
		RequestID:       input.RequestID,
		CampaignID:      input.CampaignID,
		Source:          input.Source,
		Status:          scanmodel.BatchStatusSubmitting,
		TriggerType:     scanmodel.TriggerTypeManual,
		PartIndex:       input.PartIndex,
		PartCount:       input.PartCount,
		TargetCount:     input.TargetCount,
		AcceptedTargets: len(input.AcceptedTargets),
		RejectedTargets: len(input.RejectedTargets),
		Metadata:        jsonb(input.Metadata),
	}
	targets := make([]scanmodel.Target, 0, len(input.AcceptedTargets)+len(input.RejectedTargets))
	for _, target := range input.AcceptedTargets {
		targets = append(targets, modelTargetFromCandidate(target.TargetCandidate, scanmodel.TargetStatusPending, "", ""))
	}
	for _, target := range input.RejectedTargets {
		targets = append(targets, modelTargetFromCandidate(target.TargetCandidate, scanmodel.TargetStatusRejected, target.RejectReason, target.RejectDetail))
	}
	if len(targets) == 0 {
		batch.Status = scanmodel.BatchStatusFailed
		batch.CompletedAt = &now
	}
	if err := r.repo.CreateBatch(ctx, batch, targets); err != nil {
		if isDuplicateKey(err) {
			existing, findErr := r.repo.FindBatchByRequestID(ctx, input.RequestID)
			if findErr == nil && existing != nil {
				return mapBatch(existing), nil
			}
		}
		return nil, err
	}
	return mapBatch(batch), nil
}

func (r *scanRepositoryAdapter) MarkBatchSubmitted(ctx context.Context, localBatchID uint64, input scanservice.BatchSubmittedInput) (*scanservice.Batch, error) {
	now := r.clock()
	status := normalizeBatchStatus(input.Status, scanmodel.BatchStatusQueued)
	if err := r.repo.UpdateBatchFields(ctx, localBatchID, map[string]interface{}{
		"scanner_batch_id":    nullableString(input.ScannerBatchID),
		"status":              status,
		"accepted_targets":    input.AcceptedTargets,
		"rejected_targets":    input.RejectedTargets,
		"queued_jobs":         input.QueuedJobs,
		"running_jobs":        input.RunningJobs,
		"succeeded_jobs":      input.SucceededJobs,
		"failed_jobs":         input.FailedJobs,
		"skipped_jobs":        input.SkippedJobs,
		"submitted_at":        &now,
		"raw_create_response": rawJSON(input.RawCreatePayload),
	}); err != nil {
		return nil, err
	}

	targets, err := r.loadAllTargets(ctx, localBatchID)
	if err != nil {
		return nil, err
	}
	byEndpoint := make(map[string]scanmodel.Target, len(targets))
	for _, target := range targets {
		byEndpoint[endpointKey(target.PublicIP, target.Port, target.Transport)] = target
	}
	for _, scannerTarget := range input.ScannerTargets {
		target, ok := byEndpoint[endpointKey(scannerTarget.PublicIP, scannerTarget.Port, scannerTarget.Transport)]
		if !ok {
			continue
		}
		fields := map[string]interface{}{
			"scanner_target_id": nullableString(scannerTarget.TargetID),
			"target_status":     normalizeTargetStatus(scannerTarget.Status),
			"reject_reason":     nullableString(scannerTarget.RejectReason),
			"reject_detail":     nullableString(scannerTarget.RejectDetail),
		}
		if err := r.repo.UpdateTargetFields(ctx, target.ID, fields); err != nil {
			return nil, err
		}
	}
	return r.GetBatch(ctx, localBatchID)
}

func (r *scanRepositoryAdapter) MarkBatchSubmitFailed(ctx context.Context, localBatchID uint64, scannerErr string) error {
	now := r.clock()
	return r.repo.UpdateBatchFields(ctx, localBatchID, map[string]interface{}{
		"status":        scanmodel.BatchStatusFailed,
		"scanner_error": nullableString(scannerErr),
		"completed_at":  &now,
	})
}

func (r *scanRepositoryAdapter) GetBatch(ctx context.Context, id uint64) (*scanservice.Batch, error) {
	batch, err := r.repo.GetBatch(ctx, id)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, scanservice.ErrUnknownBatch
	}
	if err != nil {
		return nil, err
	}
	out := mapBatch(batch)
	batches := []scanservice.Batch{*out}
	r.enrichBatchSummaries(ctx, batches)
	*out = batches[0]
	return out, nil
}

func (r *scanRepositoryAdapter) FindBatchForCallback(ctx context.Context, requestID string, scannerBatchID string) (*scanservice.Batch, error) {
	if strings.TrimSpace(requestID) != "" {
		batch, err := r.repo.FindBatchByRequestID(ctx, requestID)
		if err == nil {
			return mapBatch(batch), nil
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, err
		}
	}
	if strings.TrimSpace(scannerBatchID) != "" {
		batch, err := r.repo.FindBatchByScannerBatchID(ctx, scannerBatchID)
		if err == nil {
			return mapBatch(batch), nil
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, err
		}
	}
	return nil, nil
}

func (r *scanRepositoryAdapter) ListBatches(ctx context.Context, filter scanservice.ListBatchesFilter) (*scanservice.PaginatedResult[scanservice.Batch], error) {
	items, total, err := r.repo.ListBatches(ctx, scanrepository.BatchListFilter{
		Status:     filter.Status,
		Query:      filter.Query,
		CreatedBy:  filter.CreatedBy,
		From:       filter.From,
		To:         filter.To,
		Result:     filter.Result,
		AssetID:    filter.AssetID,
		PublicIP:   filter.PublicIP,
		CampaignID: filter.CampaignID,
	}, filter.Page, filter.PageSize)
	if err != nil {
		return nil, err
	}
	out := make([]scanservice.Batch, 0, len(items))
	for i := range items {
		out = append(out, *mapBatch(&items[i]))
	}
	r.enrichBatchSummaries(ctx, out)
	return &scanservice.PaginatedResult[scanservice.Batch]{Items: out, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) ListTargets(ctx context.Context, batchID uint64, filter scanservice.ListTargetsFilter) (*scanservice.PaginatedResult[scanservice.Target], error) {
	items, total, err := r.repo.ListTargets(ctx, scanrepository.TargetListFilter{
		BatchID:      batchID,
		TargetStatus: filter.TargetStatus,
		PublicIP:     filter.PublicIP,
		CAASMAssetID: filter.CAASMAssetID,
		CAASMPortID:  filter.CAASMPortID,
	}, filter.Page, filter.PageSize)
	if err != nil {
		return nil, err
	}
	out := make([]scanservice.Target, 0, len(items))
	for i := range items {
		out = append(out, *mapTarget(&items[i]))
	}
	return &scanservice.PaginatedResult[scanservice.Target]{Items: out, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) ListObservations(ctx context.Context, batchID uint64, filter scanservice.Pagination) (*scanservice.PaginatedResult[scanservice.Observation], error) {
	items, total, err := r.repo.ListObservations(ctx, scanrepository.ObservationListFilter{BatchID: batchID}, filter.Page, filter.PageSize)
	if err != nil {
		return nil, err
	}
	out := make([]scanservice.Observation, 0, len(items))
	for i := range items {
		out = append(out, *mapObservation(&items[i]))
	}
	return &scanservice.PaginatedResult[scanservice.Observation]{Items: out, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) ListCallbackReceipts(ctx context.Context, batchID uint64, filter scanservice.Pagination) (*scanservice.PaginatedResult[scanservice.CallbackReceipt], error) {
	items, total, err := r.repo.ListCallbackReceipts(ctx, scanrepository.CallbackReceiptListFilter{LocalBatchID: &batchID}, filter.Page, filter.PageSize)
	if err != nil {
		return nil, err
	}
	out := make([]scanservice.CallbackReceipt, 0, len(items))
	for i := range items {
		out = append(out, *mapCallbackReceipt(&items[i]))
	}
	return &scanservice.PaginatedResult[scanservice.CallbackReceipt]{Items: out, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) ListPortExposures(ctx context.Context, filter scanservice.ListPortExposuresFilter) (*scanservice.PaginatedResult[scanservice.PortExposure], error) {
	repoFilter := scanrepository.PortExposureFilter{
		PublicIP:    filter.PublicIP,
		Port:        filter.Port,
		AssetID:     filter.AssetID,
		Owner:       filter.Owner,
		Environment: filter.Environment,
		Result:      exposureResultFilter(filter.Result, filter.QuickFilter),
	}
	needsPostFilter := filter.Query != "" || filter.Risk != "" || filter.Service != "" || quickFilterNeedsPostFilter(filter.QuickFilter)
	page, pageSize := filter.Page, filter.PageSize
	if needsPostFilter {
		page, pageSize = 1, 200
	}
	rows, total, err := r.repo.ListLatestPortExposures(ctx, repoFilter, page, pageSize)
	if err != nil {
		return nil, err
	}
	exposures := make([]scanservice.PortExposure, 0, len(rows))
	for i := range rows {
		exposure := r.mapPortExposure(rows[i])
		if exposureMatches(exposure, filter) {
			exposures = append(exposures, exposure)
		}
	}
	if needsPostFilter {
		total = int64(len(exposures))
		exposures = paginateSlice(exposures, filter.Page, filter.PageSize)
	}
	return &scanservice.PaginatedResult[scanservice.PortExposure]{Items: exposures, Total: total, Page: filter.Page, PageSize: filter.PageSize}, nil
}

func (r *scanRepositoryAdapter) GetStats(ctx context.Context) (*scanservice.Stats, error) {
	stats := &scanservice.Stats{ResultCounts: map[string]int64{}}
	if r == nil || r.db == nil {
		return stats, nil
	}
	count := func(model any, query func(*gorm.DB) *gorm.DB) int64 {
		var value int64
		db := r.db.WithContext(ctx).Model(model)
		if query != nil {
			db = query(db)
		}
		_ = db.Count(&value).Error
		return value
	}
	stats.TotalBatches = count(&scanmodel.Batch{}, nil)
	activeBatchQuery := func(db *gorm.DB) *gorm.DB {
		return db.Where(
			"status IN ? OR (status = ? AND running_jobs > 0)",
			[]scanmodel.BatchStatus{
				scanmodel.BatchStatusSubmitting,
				scanmodel.BatchStatusQueued,
				scanmodel.BatchStatusRunning,
				scanmodel.BatchStatusSyncFailed,
			},
			scanmodel.BatchStatusCanceled,
		)
	}
	stats.ActiveBatches = count(&scanmodel.Batch{}, activeBatchQuery)
	stats.CompletedBatches = count(&scanmodel.Batch{}, func(db *gorm.DB) *gorm.DB {
		return db.Where("status = ?", scanmodel.BatchStatusCompleted)
	})
	stats.FailedBatches = count(&scanmodel.Batch{}, func(db *gorm.DB) *gorm.DB {
		return db.Where("status IN ?", []scanmodel.BatchStatus{scanmodel.BatchStatusFailed, scanmodel.BatchStatusSyncFailed})
	})
	stats.CallbackFailures = count(&scanmodel.CallbackReceipt{}, func(db *gorm.DB) *gorm.DB {
		return db.Where("status = ?", scanmodel.CallbackReceiptStatusFailed)
	})
	var jobStats struct {
		QueuedJobs    int64
		RunningJobs   int64
		SucceededJobs int64
		FailedJobs    int64
		SkippedJobs   int64
	}
	if err := r.db.WithContext(ctx).
		Model(&scanmodel.Batch{}).
		Select(`
			COALESCE(SUM(queued_jobs), 0) AS queued_jobs,
			COALESCE(SUM(running_jobs), 0) AS running_jobs,
			COALESCE(SUM(succeeded_jobs), 0) AS succeeded_jobs,
			COALESCE(SUM(failed_jobs), 0) AS failed_jobs,
			COALESCE(SUM(skipped_jobs), 0) AS skipped_jobs`).
		Scopes(activeBatchQuery).
		Scan(&jobStats).Error; err == nil {
		stats.QueuedJobs = jobStats.QueuedJobs
		stats.RunningJobs = jobStats.RunningJobs
		stats.SucceededJobs = jobStats.SucceededJobs
		stats.FailedJobs = jobStats.FailedJobs
		stats.SkippedJobs = jobStats.SkippedJobs
	}
	var latestSyncedBatch scanmodel.Batch
	if err := r.db.WithContext(ctx).
		Where("last_synced_at IS NOT NULL").
		Order("last_synced_at DESC").
		First(&latestSyncedBatch).Error; err == nil {
		stats.LatestScannerSyncAt = latestSyncedBatch.LastSyncedAt
	}

	var resultRows []struct {
		Result string
		Total  int64
	}
	if err := r.db.WithContext(ctx).
		Model(&scanmodel.Observation{}).
		Select("result, COUNT(*) AS total").
		Group("result").
		Scan(&resultRows).Error; err == nil {
		for _, row := range resultRows {
			stats.ResultCounts[row.Result] = row.Total
		}
	}
	stats.ConfirmedOpen = stats.ResultCounts[string(scanmodel.ObservationResultConfirmedOpen)]
	stats.UnknownOrFilteredCount = stats.ResultCounts[string(scanmodel.ObservationResultUnknownFiltered)]

	exposures, err := r.ListPortExposures(ctx, scanservice.ListPortExposuresFilter{Pagination: scanservice.Pagination{Page: 1, PageSize: 200}})
	if err == nil {
		for _, exposure := range exposures.Items {
			if exposure.Risk == "critical" || exposure.Risk == "high" {
				stats.HighRiskOpenCount++
			}
			if exposure.IsNew {
				stats.NewlyExposedCount++
			}
		}
	}
	return stats, nil
}

type scanCallbackSummaryRow struct {
	LocalBatchID *uint64
	CampaignID   *uint64
	Status       string
	ErrorMessage *string
	ReceivedAt   time.Time
	AppliedAt    *time.Time
}

func (r *scanRepositoryAdapter) enrichBatchSummaries(ctx context.Context, batches []scanservice.Batch) {
	if r == nil || r.db == nil || len(batches) == 0 {
		return
	}
	ids := make([]uint64, 0, len(batches))
	index := make(map[uint64]int, len(batches))
	for i := range batches {
		if batches[i].ID == 0 {
			continue
		}
		ids = append(ids, batches[i].ID)
		index[batches[i].ID] = i
	}
	if len(ids) == 0 {
		return
	}

	var rows []scanCallbackSummaryRow
	if err := r.db.WithContext(ctx).
		Model(&scanmodel.CallbackReceipt{}).
		Select("local_batch_id, status, error_message, received_at, applied_at").
		Where("local_batch_id IN ?", ids).
		Where("deleted_at IS NULL").
		Order("received_at DESC, id DESC").
		Find(&rows).Error; err != nil {
		return
	}

	failures := make(map[uint64]int64, len(ids))
	seenLatest := make(map[uint64]struct{}, len(ids))
	for _, row := range rows {
		if row.LocalBatchID == nil {
			continue
		}
		id := *row.LocalBatchID
		i, ok := index[id]
		if !ok {
			continue
		}
		if row.Status == string(scanmodel.CallbackReceiptStatusFailed) {
			failures[id]++
		}
		if _, seen := seenLatest[id]; !seen {
			batches[i].LatestCallbackStatus = row.Status
			batches[i].LatestCallbackAt = latestCallbackTime(row.ReceivedAt, row.AppliedAt)
			batches[i].LatestCallbackError = ptrString(row.ErrorMessage)
			seenLatest[id] = struct{}{}
		}
	}
	for id, count := range failures {
		if i, ok := index[id]; ok {
			batches[i].CallbackFailureCount = count
		}
	}
}

func (r *scanRepositoryAdapter) enrichCampaignSummaries(ctx context.Context, campaigns []scanservice.Campaign) {
	if r == nil || r.db == nil || len(campaigns) == 0 {
		return
	}
	ids := make([]uint64, 0, len(campaigns))
	index := make(map[uint64]int, len(campaigns))
	for i := range campaigns {
		if campaigns[i].ID == 0 {
			continue
		}
		ids = append(ids, campaigns[i].ID)
		index[campaigns[i].ID] = i
	}
	if len(ids) == 0 {
		return
	}

	var rows []scanCallbackSummaryRow
	if err := r.db.WithContext(ctx).
		Table("scan_callback_receipts AS r").
		Select("b.campaign_id, r.status, r.error_message, r.received_at, r.applied_at").
		Joins("JOIN scan_batches AS b ON b.id = r.local_batch_id AND b.deleted_at IS NULL").
		Where("b.campaign_id IN ?", ids).
		Where("r.deleted_at IS NULL").
		Order("r.received_at DESC, r.id DESC").
		Scan(&rows).Error; err != nil {
		return
	}

	failures := make(map[uint64]int64, len(ids))
	seenLatest := make(map[uint64]struct{}, len(ids))
	for _, row := range rows {
		if row.CampaignID == nil {
			continue
		}
		id := *row.CampaignID
		i, ok := index[id]
		if !ok {
			continue
		}
		if row.Status == string(scanmodel.CallbackReceiptStatusFailed) {
			failures[id]++
		}
		if _, seen := seenLatest[id]; !seen {
			campaigns[i].LatestCallbackStatus = row.Status
			campaigns[i].LatestCallbackAt = latestCallbackTime(row.ReceivedAt, row.AppliedAt)
			campaigns[i].LatestCallbackError = ptrString(row.ErrorMessage)
			seenLatest[id] = struct{}{}
		}
	}
	for id, count := range failures {
		if i, ok := index[id]; ok {
			campaigns[i].CallbackFailureCount = count
		}
	}
}

func latestCallbackTime(receivedAt time.Time, appliedAt *time.Time) *time.Time {
	if appliedAt != nil {
		return appliedAt
	}
	if receivedAt.IsZero() {
		return nil
	}
	return &receivedAt
}

func scanCancelSummary(status string, requestID string, lastSyncedAt *time.Time, updatedAt time.Time, scannerError string, metadata map[string]any) (*time.Time, string, string, string) {
	cancelStatus := firstMetadataString(metadata, "latest_cancel_status", "cancel_status")
	cancelRequestID := firstMetadataString(metadata, "cancel_request_id")
	cancelError := firstMetadataString(metadata, "cancel_error")
	cancelAt := metadataTime(metadata, "cancel_requested_at", "canceled_at")

	normalized := strings.ToLower(strings.TrimSpace(status))
	if cancelStatus == "" && normalized == "canceled" {
		cancelStatus = "canceled"
	}
	if cancelStatus == "" && normalized == "sync_failed" && strings.TrimSpace(scannerError) != "" {
		cancelStatus = "failed"
		cancelError = scannerError
	}
	if cancelRequestID == "" && cancelStatus != "" {
		cancelRequestID = strings.TrimSpace(requestID)
		if cancelRequestID != "" {
			cancelRequestID += "-cancel"
		}
	}
	if cancelAt == nil && cancelStatus != "" {
		if lastSyncedAt != nil {
			cancelAt = lastSyncedAt
		} else if !updatedAt.IsZero() {
			value := updatedAt
			cancelAt = &value
		}
	}
	return cancelAt, cancelRequestID, cancelStatus, cancelError
}

func firstMetadataString(metadata map[string]any, keys ...string) string {
	for _, key := range keys {
		value := strings.TrimSpace(stringFromMetadata(metadata, key))
		if value != "" {
			return value
		}
	}
	return ""
}

func metadataTime(metadata map[string]any, keys ...string) *time.Time {
	for _, key := range keys {
		value := strings.TrimSpace(stringFromMetadata(metadata, key))
		if value == "" {
			continue
		}
		if parsed, err := time.Parse(time.RFC3339Nano, value); err == nil {
			return &parsed
		}
		if parsed, err := time.Parse(time.RFC3339, value); err == nil {
			return &parsed
		}
	}
	return nil
}

func (r *scanRepositoryAdapter) FindCallbackReceipt(ctx context.Context, idempotencyKey string) (*scanservice.CallbackReceipt, error) {
	receipt, err := r.repo.GetCallbackReceiptByIdempotencyKey(ctx, idempotencyKey)
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return mapCallbackReceipt(receipt), nil
}

func (r *scanRepositoryAdapter) CreateCallbackReceipt(ctx context.Context, input scanservice.CallbackReceiptInput) (*scanservice.CallbackReceipt, error) {
	receipt := &scanmodel.CallbackReceipt{
		IdempotencyKey:      input.IdempotencyKey,
		RequestID:           nullableString(input.RequestID),
		ScannerBatchID:      nullableString(input.ScannerBatchID),
		LocalBatchID:        input.LocalBatchID,
		PayloadSHA256:       input.PayloadSHA256,
		HeaderPayloadSHA256: input.HeaderPayloadSHA256,
		RequestHeaderID:     nullableString(input.RequestHeaderID),
		Status:              scanmodel.CallbackReceiptStatus(input.Status),
		HTTPStatus:          input.HTTPStatus,
		ErrorMessage:        nullableString(input.ErrorMessage),
		RawPayload:          coremodel.RawJSON(input.RawPayload),
		ReceivedAt:          input.ReceivedAt,
	}
	if err := r.repo.CreateCallbackReceipt(ctx, receipt); err != nil {
		return nil, err
	}
	return mapCallbackReceipt(receipt), nil
}

func (r *scanRepositoryAdapter) MarkCallbackReceiptApplied(ctx context.Context, receiptID uint64, localBatchID uint64) error {
	now := r.clock()
	return r.repo.UpdateCallbackReceiptFields(ctx, receiptID, map[string]interface{}{
		"local_batch_id": localBatchID,
		"status":         scanmodel.CallbackReceiptStatusApplied,
		"http_status":    http.StatusOK,
		"applied_at":     &now,
	})
}

func (r *scanRepositoryAdapter) MarkCallbackReceiptFailed(ctx context.Context, receiptID uint64, localBatchID uint64, status string, httpStatus int, message string) error {
	return r.repo.UpdateCallbackReceiptFields(ctx, receiptID, map[string]interface{}{
		"local_batch_id": localBatchID,
		"status":         scanmodel.CallbackReceiptStatus(status),
		"http_status":    httpStatus,
		"error_message":  nullableString(message),
	})
}

func (r *scanRepositoryAdapter) ApplyObservations(ctx context.Context, input scanservice.ApplyObservationsInput) error {
	now := r.clock()
	return r.repo.WithTransaction(ctx, func(repo scanrepository.ScanRepository) error {
		txAdapter := &scanRepositoryAdapter{db: r.db, repo: repo, now: r.now}
		targets, err := txAdapter.loadAllTargets(ctx, input.Batch.ID)
		if err != nil {
			return err
		}
		byEndpoint := make(map[string]scanmodel.Target, len(targets))
		byScannerTarget := make(map[string]scanmodel.Target, len(targets))
		for _, target := range targets {
			byEndpoint[endpointKey(target.PublicIP, target.Port, target.Transport)] = target
			if target.ScannerTargetID != nil && *target.ScannerTargetID != "" {
				byScannerTarget[*target.ScannerTargetID] = target
			}
		}
		resultCounts := map[string]int{}
		for _, item := range input.Observations {
			target, matched := byScannerTarget[item.ScannerTargetID]
			if !matched {
				target, matched = byEndpoint[endpointKey(item.PublicIP, item.Port, item.Transport)]
			}
			observation := &scanmodel.Observation{
				BatchID:              input.Batch.ID,
				ScannerObservationID: nullableString(item.ScannerObservationID),
				ScannerTargetID:      nullableString(item.ScannerTargetID),
				ScannerJobID:         nullableString(item.ScannerJobID),
				PublicIP:             item.PublicIP,
				Port:                 item.Port,
				Transport:            strings.ToLower(firstNonEmpty(item.Transport, "tcp")),
				Result:               scanmodel.ObservationResult(item.Result),
				ReasonCode:           firstNonEmpty(item.ReasonCode, "unknown"),
				ConnectMS:            item.ConnectMS,
				ScannerID:            nullableString(item.ScannerID),
				ScannerVersion:       nullableString(item.ScannerVersion),
				ScannerSourceIP:      nullableString(item.ScannerSourceIP),
				EvidenceSHA256:       nullableString(item.EvidenceSHA256),
				EvidencePath:         nullableString(item.EvidencePath),
				StartedAt:            item.StartedAt,
				FinishedAt:           item.FinishedAt,
				ObservedAt:           firstTime(item.FinishedAt, item.StartedAt, now),
				RawPayload:           rawJSON(item.RawPayload),
				Metadata:             coremodel.JSONB{"identity_kind": item.IdentityKind, "identity_value": item.IdentityValue},
			}
			if matched {
				targetID := target.ID
				observation.TargetID = &targetID
				observation.CAASMAssetID = target.CAASMAssetID
				observation.CAASMPortID = target.CAASMPortID
				observation.Metadata = mergeJSONB(observation.Metadata, target.Metadata)
				if err := repo.UpdateTargetFields(ctx, target.ID, map[string]interface{}{
					"scanner_target_id": nullableString(firstNonEmpty(item.ScannerTargetID, ptrString(target.ScannerTargetID))),
					"target_status":     scanmodel.TargetStatusObserved,
				}); err != nil {
					return err
				}
			}
			if err := repo.UpsertObservation(ctx, observation); err != nil {
				return err
			}
			resultCounts[item.Result]++
		}
		status := normalizeBatchStatus(input.Status, scanmodel.BatchStatusRunning)
		succeededJobs := resultCounts[string(scanmodel.ObservationResultConfirmedOpen)] +
			resultCounts[string(scanmodel.ObservationResultConfirmedClosed)] +
			resultCounts[string(scanmodel.ObservationResultUnknownFiltered)]
		fields := map[string]interface{}{
			"status":         status,
			"succeeded_jobs": succeededJobs,
			"failed_jobs":    0,
			"skipped_jobs":   resultCounts[string(scanmodel.ObservationResultSkipped)],
			"last_synced_at": &now,
		}
		if input.CompletedAt != nil {
			fields["completed_at"] = input.CompletedAt
		} else if status == scanmodel.BatchStatusCompleted || status == scanmodel.BatchStatusFailed || status == scanmodel.BatchStatusCanceled {
			fields["completed_at"] = &now
		}
		if status == scanmodel.BatchStatusCompleted || status == scanmodel.BatchStatusFailed || status == scanmodel.BatchStatusCanceled {
			fields["queued_jobs"] = 0
			fields["running_jobs"] = 0
		}
		return repo.UpdateBatchFields(ctx, input.Batch.ID, fields)
	})
}

func (r *scanRepositoryAdapter) UpdateBatchFromSync(ctx context.Context, localBatchID uint64, batch *scanclient.BatchResponse, results *scanclient.ResultsResponse) error {
	now := r.clock()
	fields := map[string]interface{}{"last_synced_at": &now}
	if batch != nil {
		fields["status"] = normalizeBatchStatus(batch.Status, scanmodel.BatchStatusRunning)
		fields["queued_jobs"] = batch.QueuedJobs
		fields["running_jobs"] = batch.RunningJobs
		fields["succeeded_jobs"] = batch.SucceededJobs
		fields["failed_jobs"] = batch.FailedJobs
		fields["skipped_jobs"] = batch.SkippedJobs
		if batch.CompletedAt != nil {
			fields["completed_at"] = batch.CompletedAt
		}
	}
	if results != nil && results.CompletedAt != nil {
		fields["completed_at"] = results.CompletedAt
	}
	return r.repo.UpdateBatchFields(ctx, localBatchID, fields)
}

func (r *scanRepositoryAdapter) loadAllTargets(ctx context.Context, batchID uint64) ([]scanmodel.Target, error) {
	var out []scanmodel.Target
	page := 1
	for {
		items, _, err := r.repo.ListTargets(ctx, scanrepository.TargetListFilter{BatchID: batchID}, page, 200)
		if err != nil {
			return nil, err
		}
		out = append(out, items...)
		if len(items) < 200 {
			return out, nil
		}
		page++
	}
}

func (r *scanRepositoryAdapter) clock() time.Time {
	if r != nil && r.now != nil {
		return r.now().UTC()
	}
	return time.Now().UTC()
}

func (r *scanRepositoryAdapter) mapPortExposure(row scanrepository.PortExposureRow) scanservice.PortExposure {
	metadata := map[string]any(row.Metadata)
	observedAt := row.ObservedAt
	risk := derivePortRisk(row.Port, stringFromMetadata(metadata, "asset_criticality"))
	firstObserved := observedAt
	out := scanservice.PortExposure{
		ID:                   row.ObservationID,
		ObservationID:        row.ObservationID,
		BatchID:              row.BatchID,
		RequestID:            row.RequestID,
		PublicIP:             row.PublicIP,
		Port:                 row.Port,
		Transport:            row.Transport,
		Result:               row.Result,
		ReasonCode:           row.ReasonCode,
		Risk:                 risk,
		DerivedRisk:          risk,
		ServiceName:          stringFromMetadata(metadata, "service_name"),
		AssetName:            stringFromMetadata(metadata, "asset_name"),
		AssetService:         stringFromMetadata(metadata, "asset_service"),
		Owner:                ptrString(row.TargetOwner),
		Environment:          ptrString(row.TargetEnvironment),
		CAASMAssetID:         row.CAASMAssetID,
		CAASMPortID:          row.CAASMPortID,
		ScannerBatchID:       ptrString(row.ScannerBatchID),
		ScannerSourceIP:      ptrString(row.ScannerSourceIP),
		ConnectMS:            row.ConnectMS,
		EvidenceSHA256:       ptrString(row.EvidenceSHA256),
		EvidencePath:         ptrString(row.EvidencePath),
		EvidenceSummary:      evidenceSummary(row),
		ObservedAt:           observedAt,
		LastObserved:         observedAt,
		LastConfirmed:        observedAt,
		FirstObserved:        &firstObserved,
		IsNew:                observedAt.After(r.clock().Add(-24 * time.Hour)),
		Metadata:             metadata,
		ScannerTargetID:      ptrString(row.ScannerTargetID),
		ScannerObservationID: ptrString(row.ScannerObservationID),
		ScannerJobID:         ptrString(row.ScannerJobID),
	}
	if out.Owner == "" {
		out.Owner = stringFromMetadata(metadata, "asset_service")
	}
	return out
}

func modelTargetFromCandidate(candidate scanservice.TargetCandidate, status scanmodel.TargetStatus, rejectReason string, rejectDetail string) scanmodel.Target {
	return scanmodel.Target{
		CAASMAssetID:   candidate.CAASMAssetID,
		CAASMIPAssetID: candidate.CAASMIPAssetID,
		CAASMPortID:    candidate.CAASMPortID,
		ScannerAssetID: candidate.ScannerAssetID,
		PublicIP:       candidate.PublicIP,
		Port:           candidate.Port,
		Transport:      strings.ToLower(firstNonEmpty(candidate.Transport, "tcp")),
		Domain:         nullableString(candidate.Domain),
		CloudProvider:  nullableString(candidate.CloudProvider),
		CloudAccountID: nullableString(candidate.CloudAccountID),
		Region:         nullableString(candidate.Region),
		ResourceType:   nullableString(candidate.ResourceType),
		ResourceID:     nullableString(candidate.ResourceID),
		Owner:          nullableString(candidate.Owner),
		Environment:    nullableString(candidate.Environment),
		AssetSnapshot:  rawJSON(candidate.AssetSnapshot),
		PortSnapshot:   rawJSON(candidate.PortSnapshot),
		TargetStatus:   status,
		RejectReason:   nullableString(rejectReason),
		RejectDetail:   nullableString(rejectDetail),
		Metadata:       mergeJSONB(jsonb(candidate.Metadata), jsonb(map[string]any{"asset_snapshot": candidate.AssetSnapshot, "port_snapshot": candidate.PortSnapshot})),
	}
}

func mapBatch(batch *scanmodel.Batch) *scanservice.Batch {
	if batch == nil {
		return nil
	}
	metadata := map[string]any(batch.Metadata)
	cancelRequestedAt, cancelRequestID, latestCancelStatus, cancelError := scanCancelSummary(
		string(batch.Status),
		batch.RequestID,
		batch.LastSyncedAt,
		batch.UpdatedAt,
		ptrString(batch.ScannerError),
		metadata,
	)
	return &scanservice.Batch{
		ID:                 batch.ID,
		RequestID:          batch.RequestID,
		ScannerBatchID:     ptrString(batch.ScannerBatchID),
		CampaignID:         batch.CampaignID,
		Source:             batch.Source,
		Status:             string(batch.Status),
		PartIndex:          batch.PartIndex,
		PartCount:          batch.PartCount,
		TargetCount:        batch.TargetCount,
		AcceptedTargets:    batch.AcceptedTargets,
		RejectedTargets:    batch.RejectedTargets,
		QueuedJobs:         batch.QueuedJobs,
		RunningJobs:        batch.RunningJobs,
		SucceededJobs:      batch.SucceededJobs,
		FailedJobs:         batch.FailedJobs,
		SkippedJobs:        batch.SkippedJobs,
		CancelRequestedAt:  cancelRequestedAt,
		CancelRequestID:    cancelRequestID,
		LatestCancelStatus: latestCancelStatus,
		CancelError:        cancelError,
		SubmittedAt:        batch.SubmittedAt,
		CompletedAt:        batch.CompletedAt,
		LastSyncedAt:       batch.LastSyncedAt,
		ScannerError:       ptrString(batch.ScannerError),
		Metadata:           metadata,
		CreatedAt:          batch.CreatedAt,
		UpdatedAt:          batch.UpdatedAt,
	}
}

func mapCampaign(campaign *scanmodel.Campaign) *scanservice.Campaign {
	if campaign == nil {
		return nil
	}
	metadata := map[string]any(campaign.Metadata)
	cancelRequestedAt, cancelRequestID, latestCancelStatus, cancelError := scanCancelSummary(
		string(campaign.Status),
		campaign.RequestID,
		campaign.LastSyncedAt,
		campaign.UpdatedAt,
		ptrString(campaign.ScannerError),
		metadata,
	)
	return &scanservice.Campaign{
		ID:                 campaign.ID,
		RequestID:          campaign.RequestID,
		Source:             campaign.Source,
		TargetScope:        campaign.TargetScope,
		Status:             string(campaign.Status),
		TargetCount:        campaign.TargetCount,
		AcceptedTargets:    campaign.AcceptedTargets,
		RejectedTargets:    campaign.RejectedTargets,
		QueuedJobs:         campaign.QueuedJobs,
		RunningJobs:        campaign.RunningJobs,
		SucceededJobs:      campaign.SucceededJobs,
		FailedJobs:         campaign.FailedJobs,
		SkippedJobs:        campaign.SkippedJobs,
		BatchCount:         campaign.BatchCount,
		CompletedBatches:   campaign.CompletedBatches,
		FailedBatches:      campaign.FailedBatches,
		NextCursor:         campaign.NextCursor,
		CancelRequestedAt:  cancelRequestedAt,
		CancelRequestID:    cancelRequestID,
		LatestCancelStatus: latestCancelStatus,
		CancelError:        cancelError,
		SubmittedAt:        campaign.SubmittedAt,
		CompletedAt:        campaign.CompletedAt,
		LastSyncedAt:       campaign.LastSyncedAt,
		ScannerError:       ptrString(campaign.ScannerError),
		Metadata:           metadata,
		CreatedAt:          campaign.CreatedAt,
		UpdatedAt:          campaign.UpdatedAt,
	}
}

func mapTarget(target *scanmodel.Target) *scanservice.Target {
	if target == nil {
		return nil
	}
	return &scanservice.Target{
		ID:              target.ID,
		BatchID:         target.BatchID,
		CAASMAssetID:    target.CAASMAssetID,
		CAASMIPAssetID:  target.CAASMIPAssetID,
		CAASMPortID:     target.CAASMPortID,
		ScannerAssetID:  target.ScannerAssetID,
		ScannerTargetID: ptrString(target.ScannerTargetID),
		PublicIP:        target.PublicIP,
		Port:            target.Port,
		Transport:       target.Transport,
		TargetStatus:    string(target.TargetStatus),
		RejectReason:    ptrString(target.RejectReason),
		RejectDetail:    ptrString(target.RejectDetail),
		Metadata:        map[string]any(target.Metadata),
	}
}

func mapObservation(observation *scanmodel.Observation) *scanservice.Observation {
	if observation == nil {
		return nil
	}
	return &scanservice.Observation{
		ID:                   observation.ID,
		BatchID:              observation.BatchID,
		TargetID:             observation.TargetID,
		ScannerObservationID: ptrString(observation.ScannerObservationID),
		ScannerTargetID:      ptrString(observation.ScannerTargetID),
		ScannerJobID:         ptrString(observation.ScannerJobID),
		PublicIP:             observation.PublicIP,
		Port:                 observation.Port,
		Transport:            observation.Transport,
		Result:               string(observation.Result),
		ReasonCode:           observation.ReasonCode,
		ConnectMS:            observation.ConnectMS,
		ScannerSourceIP:      ptrString(observation.ScannerSourceIP),
		EvidenceSHA256:       ptrString(observation.EvidenceSHA256),
		EvidencePath:         ptrString(observation.EvidencePath),
		ObservedAt:           observation.ObservedAt,
	}
}

func mapCallbackReceipt(receipt *scanmodel.CallbackReceipt) *scanservice.CallbackReceipt {
	if receipt == nil {
		return nil
	}
	return &scanservice.CallbackReceipt{
		ID:                  receipt.ID,
		IdempotencyKey:      receipt.IdempotencyKey,
		RequestID:           ptrString(receipt.RequestID),
		ScannerBatchID:      ptrString(receipt.ScannerBatchID),
		LocalBatchID:        receipt.LocalBatchID,
		PayloadSHA256:       receipt.PayloadSHA256,
		HeaderPayloadSHA256: receipt.HeaderPayloadSHA256,
		RequestHeaderID:     ptrString(receipt.RequestHeaderID),
		Status:              string(receipt.Status),
		HTTPStatus:          receipt.HTTPStatus,
		ErrorMessage:        ptrString(receipt.ErrorMessage),
		ReceivedAt:          receipt.ReceivedAt,
		AppliedAt:           receipt.AppliedAt,
	}
}

func exposureResultFilter(result string, quickFilter string) string {
	result = strings.TrimSpace(result)
	if result == "all_exposed" {
		return string(scanmodel.ObservationResultConfirmedOpen)
	}
	if result != "" {
		return result
	}
	if strings.TrimSpace(quickFilter) == "needs_rescan" {
		return string(scanmodel.ObservationResultUnknownFiltered)
	}
	return string(scanmodel.ObservationResultConfirmedOpen)
}

func quickFilterNeedsPostFilter(filter string) bool {
	switch strings.TrimSpace(filter) {
	case "high_risk", "admin_ports", "databases", "newly_exposed", "production", "no_owner":
		return true
	default:
		return false
	}
}

func exposureMatches(item scanservice.PortExposure, filter scanservice.ListPortExposuresFilter) bool {
	query := strings.ToLower(strings.TrimSpace(filter.Query))
	if query != "" {
		haystack := strings.ToLower(strings.Join([]string{
			item.PublicIP,
			strconv.Itoa(item.Port),
			item.ServiceName,
			item.AssetName,
			item.AssetService,
			item.Owner,
			item.Environment,
			item.ScannerBatchID,
		}, " "))
		if !strings.Contains(haystack, query) {
			return false
		}
	}
	if filter.Risk != "" && item.Risk != strings.TrimSpace(filter.Risk) {
		return false
	}
	if filter.Service != "" && !strings.Contains(strings.ToLower(item.ServiceName), strings.ToLower(strings.TrimSpace(filter.Service))) {
		return false
	}
	switch strings.TrimSpace(filter.QuickFilter) {
	case "high_risk":
		return item.Risk == "critical" || item.Risk == "high"
	case "admin_ports":
		return isAdminPort(item.Port)
	case "databases":
		return isDatabasePort(item.Port) || strings.Contains(strings.ToLower(item.ServiceName), "database")
	case "newly_exposed":
		return item.IsNew
	case "production":
		return strings.EqualFold(item.Environment, "production") || strings.EqualFold(item.Environment, "prod")
	case "no_owner":
		return strings.TrimSpace(item.Owner) == ""
	default:
		return true
	}
}

func paginateSlice(items []scanservice.PortExposure, page int, pageSize int) []scanservice.PortExposure {
	if page <= 0 {
		page = 1
	}
	if pageSize <= 0 {
		pageSize = 20
	}
	start := (page - 1) * pageSize
	if start >= len(items) {
		return []scanservice.PortExposure{}
	}
	end := start + pageSize
	if end > len(items) {
		end = len(items)
	}
	return items[start:end]
}

func derivePortRisk(port int, criticality string) string {
	if isDatabasePort(port) || isAdminPort(port) {
		return "critical"
	}
	switch port {
	case 9200, 9300, 11211, 5672, 15672, 2375, 2376, 6443:
		return "high"
	case 80, 443, 8080, 8443:
		if strings.EqualFold(criticality, "critical") || strings.EqualFold(criticality, "high") {
			return "high"
		}
		return "medium"
	default:
		return "medium"
	}
}

func isAdminPort(port int) bool {
	switch port {
	case 22, 23, 3389, 5900, 5985, 5986, 8081, 9000:
		return true
	default:
		return false
	}
}

func isDatabasePort(port int) bool {
	switch port {
	case 1433, 1521, 27017, 3306, 5432, 6379, 9200:
		return true
	default:
		return false
	}
}

func evidenceSummary(row scanrepository.PortExposureRow) string {
	source := ptrString(row.ScannerSourceIP)
	if source == "" {
		return "Scanner confirmed public reachability"
	}
	return "Scanner confirmed public reachability from source " + source
}

func endpointKey(ip string, port int, transport string) string {
	return strings.TrimSpace(ip) + ":" + strconv.Itoa(port) + "/" + strings.ToLower(firstNonEmpty(transport, "tcp"))
}

func normalizeBatchStatus(status string, fallback scanmodel.BatchStatus) scanmodel.BatchStatus {
	status = strings.ToLower(strings.TrimSpace(status))
	if status == "" {
		return fallback
	}
	return scanmodel.BatchStatus(status)
}

func normalizeTargetStatus(status string) scanmodel.TargetStatus {
	status = strings.ToLower(strings.TrimSpace(status))
	switch scanmodel.TargetStatus(status) {
	case scanmodel.TargetStatusAccepted, scanmodel.TargetStatusRejected, scanmodel.TargetStatusObserved, scanmodel.TargetStatusSyncMissing:
		return scanmodel.TargetStatus(status)
	default:
		return scanmodel.TargetStatusAccepted
	}
}

func rawJSON(value any) coremodel.RawJSON {
	if value == nil {
		return nil
	}
	data, err := json.Marshal(value)
	if err != nil {
		return nil
	}
	return coremodel.RawJSON(data)
}

func jsonb(value map[string]any) coremodel.JSONB {
	if value == nil {
		return coremodel.JSONB{}
	}
	return coremodel.JSONB(value)
}

func mergeJSONB(base coremodel.JSONB, extra coremodel.JSONB) coremodel.JSONB {
	out := coremodel.JSONB{}
	for key, value := range base {
		out[key] = value
	}
	for key, value := range extra {
		if value != nil {
			out[key] = value
		}
	}
	return out
}

func nullableString(value string) *string {
	value = strings.TrimSpace(value)
	if value == "" {
		return nil
	}
	return &value
}

func ptrString(value *string) string {
	if value == nil {
		return ""
	}
	return strings.TrimSpace(*value)
}

func uint64Ptr(value uint64) *uint64 {
	return &value
}

func firstNonEmpty(values ...string) string {
	for _, value := range values {
		if strings.TrimSpace(value) != "" {
			return strings.TrimSpace(value)
		}
	}
	return ""
}

func firstTime(values ...any) time.Time {
	for _, value := range values {
		switch typed := value.(type) {
		case *time.Time:
			if typed != nil && !typed.IsZero() {
				return typed.UTC()
			}
		case time.Time:
			if !typed.IsZero() {
				return typed.UTC()
			}
		}
	}
	return time.Now().UTC()
}

func stringFromMetadata(metadata map[string]any, key string) string {
	if metadata == nil {
		return ""
	}
	value, ok := metadata[key]
	if !ok || value == nil {
		return ""
	}
	switch typed := value.(type) {
	case string:
		return strings.TrimSpace(typed)
	default:
		return strings.TrimSpace(fmt.Sprint(typed))
	}
}

func isDuplicateKey(err error) bool {
	if err == nil {
		return false
	}
	text := strings.ToLower(err.Error())
	return strings.Contains(text, "duplicate") || strings.Contains(text, "unique")
}