package repository

import (
	"context"
	"errors"
	"strings"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	coremodel "security-center-backend/internal/model"
	scanmodel "security-center-backend/internal/modules/scan/model"
)

const maxPageSize = 200

type CampaignListFilter struct {
	Status    string
	Query     string
	CreatedBy *uint64
	From      *time.Time
	To        *time.Time
}

type BatchListFilter struct {
	Status     string
	Query      string
	CreatedBy  *uint64
	From       *time.Time
	To         *time.Time
	Result     string
	AssetID    *uint64
	PublicIP   string
	CampaignID *uint64
}

type TargetListFilter struct {
	BatchID      uint64
	TargetStatus string
	PublicIP     string
	CAASMAssetID *uint64
	CAASMPortID  *uint64
}

type ObservationListFilter struct {
	BatchID      uint64
	TargetID     *uint64
	Result       string
	CAASMAssetID *uint64
	CAASMPortID  *uint64
	PublicIP     string
}

type CallbackReceiptListFilter struct {
	LocalBatchID *uint64
	Status       string
}

type PortExposureFilter struct {
	PublicIP    string
	Port        *int
	Transport   string
	AssetID     *uint64
	CAASMPortID *uint64
	Owner       string
	Environment string
	Result      string
}

type PortExposureRow struct {
	ObservationID        uint64            `gorm:"column:observation_id"`
	BatchID              uint64            `gorm:"column:batch_id"`
	RequestID            string            `gorm:"column:request_id"`
	ScannerBatchID       *string           `gorm:"column:scanner_batch_id"`
	TargetID             *uint64           `gorm:"column:target_id"`
	CAASMAssetID         *uint64           `gorm:"column:caasm_asset_id"`
	CAASMPortID          *uint64           `gorm:"column:caasm_port_id"`
	PublicIP             string            `gorm:"column:public_ip"`
	Port                 int               `gorm:"column:port"`
	Transport            string            `gorm:"column:transport"`
	Result               string            `gorm:"column:result"`
	ReasonCode           string            `gorm:"column:reason_code"`
	ConnectMS            int64             `gorm:"column:connect_ms"`
	ScannerSourceIP      *string           `gorm:"column:scanner_source_ip"`
	EvidenceSHA256       *string           `gorm:"column:evidence_sha256"`
	EvidencePath         *string           `gorm:"column:evidence_path"`
	ObservedAt           time.Time         `gorm:"column:observed_at"`
	ScannerTargetID      *string           `gorm:"column:scanner_target_id"`
	ScannerObservationID *string           `gorm:"column:scanner_observation_id"`
	ScannerJobID         *string           `gorm:"column:scanner_job_id"`
	TargetOwner          *string           `gorm:"column:target_owner"`
	TargetEnvironment    *string           `gorm:"column:target_environment"`
	TargetDomain         *string           `gorm:"column:target_domain"`
	RawPayload           coremodel.RawJSON `gorm:"column:raw_payload"`
	Metadata             coremodel.JSONB   `gorm:"column:metadata"`
}

type ScanRepository interface {
	WithTransaction(ctx context.Context, fn func(repo ScanRepository) error) error
	CreateCampaign(ctx context.Context, campaign *scanmodel.Campaign) error
	ListCampaigns(ctx context.Context, filter CampaignListFilter, page, pageSize int) ([]scanmodel.Campaign, int64, error)
	GetCampaign(ctx context.Context, id uint64) (*scanmodel.Campaign, error)
	FindCampaignByRequestID(ctx context.Context, requestID string) (*scanmodel.Campaign, error)
	ClaimNextCampaign(ctx context.Context) (*scanmodel.Campaign, error)
	UpdateCampaignFields(ctx context.Context, id uint64, fields map[string]interface{}) error
	UpdateCampaignBatchPartCount(ctx context.Context, campaignID uint64, partCount int) error
	CreateBatch(ctx context.Context, batch *scanmodel.Batch, targets []scanmodel.Target) error
	ListBatches(ctx context.Context, filter BatchListFilter, page, pageSize int) ([]scanmodel.Batch, int64, error)
	GetBatch(ctx context.Context, id uint64) (*scanmodel.Batch, error)
	FindBatchByRequestID(ctx context.Context, requestID string) (*scanmodel.Batch, error)
	FindBatchByScannerBatchID(ctx context.Context, scannerBatchID string) (*scanmodel.Batch, error)
	UpdateBatchFields(ctx context.Context, id uint64, fields map[string]interface{}) error
	CreateTargets(ctx context.Context, targets []scanmodel.Target) error
	ListTargets(ctx context.Context, filter TargetListFilter, page, pageSize int) ([]scanmodel.Target, int64, error)
	GetTarget(ctx context.Context, id uint64) (*scanmodel.Target, error)
	UpdateTargetFields(ctx context.Context, id uint64, fields map[string]interface{}) error
	UpsertObservation(ctx context.Context, observation *scanmodel.Observation) error
	ListObservations(ctx context.Context, filter ObservationListFilter, page, pageSize int) ([]scanmodel.Observation, int64, error)
	GetObservation(ctx context.Context, id uint64) (*scanmodel.Observation, error)
	ListLatestPortExposures(ctx context.Context, filter PortExposureFilter, page, pageSize int) ([]PortExposureRow, int64, error)
	CreateCallbackReceipt(ctx context.Context, receipt *scanmodel.CallbackReceipt) error
	GetCallbackReceiptByID(ctx context.Context, id uint64) (*scanmodel.CallbackReceipt, error)
	GetCallbackReceiptByIdempotencyKey(ctx context.Context, idempotencyKey string) (*scanmodel.CallbackReceipt, error)
	ListCallbackReceipts(ctx context.Context, filter CallbackReceiptListFilter, page, pageSize int) ([]scanmodel.CallbackReceipt, int64, error)
	UpdateCallbackReceiptFields(ctx context.Context, id uint64, fields map[string]interface{}) error
}

type GormScanRepository struct {
	db *gorm.DB
}

func NewScanRepository(db *gorm.DB) ScanRepository {
	return &GormScanRepository{db: db}
}

func (r *GormScanRepository) WithTransaction(ctx context.Context, fn func(repo ScanRepository) error) error {
	if fn == nil {
		return errors.New("scan repository: transaction function is required")
	}
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		return fn(&GormScanRepository{db: tx})
	})
}

func (r *GormScanRepository) CreateCampaign(ctx context.Context, campaign *scanmodel.Campaign) error {
	if campaign == nil {
		return errors.New("scan repository: nil campaign")
	}
	normalizeCampaign(campaign)
	return r.db.WithContext(ctx).Create(campaign).Error
}

func (r *GormScanRepository) ListCampaigns(ctx context.Context, filter CampaignListFilter, page, pageSize int) ([]scanmodel.Campaign, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)
	query := r.db.WithContext(ctx).Model(&scanmodel.Campaign{})
	query = applyCampaignFilter(query, filter)

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var campaigns []scanmodel.Campaign
	if err := query.
		Order("created_at DESC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&campaigns).Error; err != nil {
		return nil, 0, err
	}
	return campaigns, total, nil
}

func (r *GormScanRepository) GetCampaign(ctx context.Context, id uint64) (*scanmodel.Campaign, error) {
	var campaign scanmodel.Campaign
	if err := r.db.WithContext(ctx).First(&campaign, "id = ?", id).Error; err != nil {
		return nil, err
	}
	return &campaign, nil
}

func (r *GormScanRepository) FindCampaignByRequestID(ctx context.Context, requestID string) (*scanmodel.Campaign, error) {
	var campaign scanmodel.Campaign
	if err := r.db.WithContext(ctx).
		Where("request_id = ?", strings.TrimSpace(requestID)).
		First(&campaign).Error; err != nil {
		return nil, err
	}
	return &campaign, nil
}

func (r *GormScanRepository) ClaimNextCampaign(ctx context.Context) (*scanmodel.Campaign, error) {
	var campaign scanmodel.Campaign
	err := r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("status = ?", scanmodel.CampaignStatusQueued).
			Order("created_at ASC, id ASC").
			First(&campaign).Error; err != nil {
			return err
		}
		return tx.Model(&scanmodel.Campaign{}).
			Where("id = ? AND status = ?", campaign.ID, scanmodel.CampaignStatusQueued).
			Updates(map[string]interface{}{
				"status":       scanmodel.CampaignStatusDispatching,
				"submitted_at": time.Now().UTC(),
			}).Error
	})
	if err != nil {
		return nil, err
	}
	campaign.Status = scanmodel.CampaignStatusDispatching
	return &campaign, nil
}

func (r *GormScanRepository) UpdateCampaignFields(ctx context.Context, id uint64, fields map[string]interface{}) error {
	if len(fields) == 0 {
		return nil
	}
	return r.db.WithContext(ctx).
		Model(&scanmodel.Campaign{}).
		Where("id = ?", id).
		Updates(fields).Error
}

func (r *GormScanRepository) UpdateCampaignBatchPartCount(ctx context.Context, campaignID uint64, partCount int) error {
	if campaignID == 0 || partCount <= 0 {
		return nil
	}
	return r.db.WithContext(ctx).
		Model(&scanmodel.Batch{}).
		Where("campaign_id = ?", campaignID).
		Update("part_count", partCount).Error
}

func (r *GormScanRepository) CreateBatch(ctx context.Context, batch *scanmodel.Batch, targets []scanmodel.Target) error {
	if batch == nil {
		return errors.New("scan repository: nil batch")
	}
	normalizeBatch(batch)
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Create(batch).Error; err != nil {
			return err
		}
		for i := range targets {
			if targets[i].BatchID == 0 {
				targets[i].BatchID = batch.ID
			}
			normalizeTarget(&targets[i])
		}
		if len(targets) == 0 {
			return nil
		}
		return tx.CreateInBatches(targets, 100).Error
	})
}

func (r *GormScanRepository) ListBatches(ctx context.Context, filter BatchListFilter, page, pageSize int) ([]scanmodel.Batch, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)
	query := r.db.WithContext(ctx).Model(&scanmodel.Batch{})
	query = applyBatchFilter(query, filter)

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var batches []scanmodel.Batch
	ordering := "created_at DESC"
	if filter.CampaignID != nil {
		ordering = "part_index ASC, created_at ASC, id ASC"
	}
	if err := query.
		Order(ordering).
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&batches).Error; err != nil {
		return nil, 0, err
	}
	return batches, total, nil
}

func (r *GormScanRepository) GetBatch(ctx context.Context, id uint64) (*scanmodel.Batch, error) {
	var batch scanmodel.Batch
	if err := r.db.WithContext(ctx).First(&batch, "id = ?", id).Error; err != nil {
		return nil, err
	}
	return &batch, nil
}

func (r *GormScanRepository) FindBatchByRequestID(ctx context.Context, requestID string) (*scanmodel.Batch, error) {
	var batch scanmodel.Batch
	if err := r.db.WithContext(ctx).
		Where("request_id = ?", strings.TrimSpace(requestID)).
		First(&batch).Error; err != nil {
		return nil, err
	}
	return &batch, nil
}

func (r *GormScanRepository) FindBatchByScannerBatchID(ctx context.Context, scannerBatchID string) (*scanmodel.Batch, error) {
	var batch scanmodel.Batch
	if err := r.db.WithContext(ctx).
		Where("scanner_batch_id = ?", strings.TrimSpace(scannerBatchID)).
		First(&batch).Error; err != nil {
		return nil, err
	}
	return &batch, nil
}

func (r *GormScanRepository) UpdateBatchFields(ctx context.Context, id uint64, fields map[string]interface{}) error {
	if len(fields) == 0 {
		return nil
	}
	return r.db.WithContext(ctx).
		Model(&scanmodel.Batch{}).
		Where("id = ?", id).
		Updates(fields).Error
}

func (r *GormScanRepository) CreateTargets(ctx context.Context, targets []scanmodel.Target) error {
	if len(targets) == 0 {
		return nil
	}
	for i := range targets {
		normalizeTarget(&targets[i])
	}
	return r.db.WithContext(ctx).CreateInBatches(targets, 100).Error
}

func (r *GormScanRepository) ListTargets(ctx context.Context, filter TargetListFilter, page, pageSize int) ([]scanmodel.Target, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)
	query := r.db.WithContext(ctx).Model(&scanmodel.Target{})
	query = applyTargetFilter(query, filter)

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var targets []scanmodel.Target
	if err := query.
		Order("created_at ASC, id ASC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&targets).Error; err != nil {
		return nil, 0, err
	}
	return targets, total, nil
}

func (r *GormScanRepository) GetTarget(ctx context.Context, id uint64) (*scanmodel.Target, error) {
	var target scanmodel.Target
	if err := r.db.WithContext(ctx).First(&target, "id = ?", id).Error; err != nil {
		return nil, err
	}
	return &target, nil
}

func (r *GormScanRepository) UpdateTargetFields(ctx context.Context, id uint64, fields map[string]interface{}) error {
	if len(fields) == 0 {
		return nil
	}
	return r.db.WithContext(ctx).
		Model(&scanmodel.Target{}).
		Where("id = ?", id).
		Updates(fields).Error
}

func (r *GormScanRepository) UpsertObservation(ctx context.Context, observation *scanmodel.Observation) error {
	if observation == nil {
		return errors.New("scan repository: nil observation")
	}
	normalizeObservation(observation)
	conflictColumns := observationConflictColumns(observation)
	if len(conflictColumns) == 0 {
		return errors.New("scan repository: observation identity is required")
	}
	return r.db.WithContext(ctx).Clauses(clause.OnConflict{
		Columns: conflictColumns,
		DoUpdates: clause.AssignmentColumns([]string{
			"target_id",
			"scanner_observation_id",
			"scanner_target_id",
			"scanner_job_id",
			"caasm_asset_id",
			"caasm_port_id",
			"public_ip",
			"port",
			"transport",
			"result",
			"reason_code",
			"connect_ms",
			"scanner_id",
			"scanner_version",
			"scanner_source_ip",
			"evidence_sha256",
			"evidence_path",
			"started_at",
			"finished_at",
			"observed_at",
			"raw_payload",
			"metadata",
			"deleted_at",
			"updated_at",
		}),
	}).Create(observation).Error
}

func (r *GormScanRepository) ListObservations(ctx context.Context, filter ObservationListFilter, page, pageSize int) ([]scanmodel.Observation, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)
	query := r.db.WithContext(ctx).Model(&scanmodel.Observation{})
	query = applyObservationFilter(query, filter)

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var observations []scanmodel.Observation
	if err := query.
		Order("observed_at DESC, id DESC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&observations).Error; err != nil {
		return nil, 0, err
	}
	return observations, total, nil
}

func (r *GormScanRepository) GetObservation(ctx context.Context, id uint64) (*scanmodel.Observation, error) {
	var observation scanmodel.Observation
	if err := r.db.WithContext(ctx).First(&observation, "id = ?", id).Error; err != nil {
		return nil, err
	}
	return &observation, nil
}

func (r *GormScanRepository) ListLatestPortExposures(ctx context.Context, filter PortExposureFilter, page, pageSize int) ([]PortExposureRow, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)

	baseQuery := func() *gorm.DB {
		query := r.db.WithContext(ctx).
			Table("scan_observations AS o").
			Joins("JOIN scan_batches AS b ON b.id = o.batch_id AND b.deleted_at IS NULL").
			Joins("LEFT JOIN scan_targets AS t ON t.id = o.target_id AND t.deleted_at IS NULL").
			Where("o.deleted_at IS NULL").
			Where(`NOT EXISTS (
				SELECT 1
				FROM scan_observations AS newer
				WHERE newer.deleted_at IS NULL
					  AND newer.public_ip = o.public_ip
					  AND newer.port = o.port
					  AND newer.transport = o.transport
					  AND newer.result = o.result
					  AND (
						newer.observed_at > o.observed_at
						OR (newer.observed_at = o.observed_at AND newer.id > o.id)
				  )
			)`)
		return applyPortExposureFilter(query, filter)
	}

	var total int64
	if err := baseQuery().Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var rows []PortExposureRow
	if err := baseQuery().
		Select(`o.id AS observation_id,
			o.batch_id,
			b.request_id,
			b.scanner_batch_id,
			o.target_id,
			o.caasm_asset_id,
			o.caasm_port_id,
			o.public_ip,
			o.port,
			o.transport,
			o.result,
			o.reason_code,
			o.connect_ms,
			o.scanner_source_ip,
			o.evidence_sha256,
			o.evidence_path,
			o.observed_at,
			o.scanner_target_id,
			o.scanner_observation_id,
			o.scanner_job_id,
			t.owner AS target_owner,
			t.environment AS target_environment,
			t.domain AS target_domain,
			o.raw_payload,
			o.metadata`).
		Order("o.observed_at DESC, o.id DESC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Scan(&rows).Error; err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}

func (r *GormScanRepository) CreateCallbackReceipt(ctx context.Context, receipt *scanmodel.CallbackReceipt) error {
	if receipt == nil {
		return errors.New("scan repository: nil callback receipt")
	}
	normalizeCallbackReceipt(receipt)
	return r.db.WithContext(ctx).Create(receipt).Error
}

func (r *GormScanRepository) GetCallbackReceiptByID(ctx context.Context, id uint64) (*scanmodel.CallbackReceipt, error) {
	var receipt scanmodel.CallbackReceipt
	if err := r.db.WithContext(ctx).First(&receipt, "id = ?", id).Error; err != nil {
		return nil, err
	}
	return &receipt, nil
}

func (r *GormScanRepository) GetCallbackReceiptByIdempotencyKey(ctx context.Context, idempotencyKey string) (*scanmodel.CallbackReceipt, error) {
	var receipt scanmodel.CallbackReceipt
	if err := r.db.WithContext(ctx).
		Where("idempotency_key = ?", strings.TrimSpace(idempotencyKey)).
		First(&receipt).Error; err != nil {
		return nil, err
	}
	return &receipt, nil
}

func (r *GormScanRepository) ListCallbackReceipts(ctx context.Context, filter CallbackReceiptListFilter, page, pageSize int) ([]scanmodel.CallbackReceipt, int64, error) {
	page, pageSize = normalizePagination(page, pageSize)
	query := r.db.WithContext(ctx).Model(&scanmodel.CallbackReceipt{})
	if filter.LocalBatchID != nil {
		query = query.Where("local_batch_id = ?", *filter.LocalBatchID)
	}
	if filter.Status != "" {
		switch strings.ToLower(strings.TrimSpace(filter.Status)) {
		case "active":
			query = query.Where(
				"status IN ? OR (status = ? AND running_jobs > 0)",
				[]string{
					string(scanmodel.BatchStatusSubmitting),
					string(scanmodel.BatchStatusQueued),
					string(scanmodel.BatchStatusRunning),
					string(scanmodel.BatchStatusSyncFailed),
				},
				string(scanmodel.BatchStatusCanceled),
			)
		default:
			query = query.Where("status = ?", filter.Status)
		}
	}

	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	var receipts []scanmodel.CallbackReceipt
	if err := query.
		Order("received_at DESC, id DESC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&receipts).Error; err != nil {
		return nil, 0, err
	}
	return receipts, total, nil
}

func (r *GormScanRepository) UpdateCallbackReceiptFields(ctx context.Context, id uint64, fields map[string]interface{}) error {
	if len(fields) == 0 {
		return nil
	}
	return r.db.WithContext(ctx).
		Model(&scanmodel.CallbackReceipt{}).
		Where("id = ?", id).
		Updates(fields).Error
}

func applyBatchFilter(query *gorm.DB, filter BatchListFilter) *gorm.DB {
	if filter.CampaignID != nil {
		query = query.Where("campaign_id = ?", *filter.CampaignID)
	}
	if filter.Status != "" {
		switch strings.ToLower(strings.TrimSpace(filter.Status)) {
		case "active":
			query = query.Where(
				"status IN ? OR (status = ? AND running_jobs > 0)",
				[]string{
					string(scanmodel.CampaignStatusQueued),
					string(scanmodel.CampaignStatusDispatching),
					string(scanmodel.CampaignStatusRunning),
					string(scanmodel.CampaignStatusSyncFailed),
				},
				string(scanmodel.CampaignStatusCanceled),
			)
		default:
			query = query.Where("status = ?", filter.Status)
		}
	}
	if filter.Query != "" {
		like := "%" + strings.TrimSpace(filter.Query) + "%"
		query = query.Where("request_id LIKE ? OR scanner_batch_id LIKE ?", like, like)
	}
	if filter.CreatedBy != nil {
		query = query.Where("created_by = ?", *filter.CreatedBy)
	}
	if filter.From != nil {
		query = query.Where("created_at >= ?", *filter.From)
	}
	if filter.To != nil {
		query = query.Where("created_at <= ?", *filter.To)
	}
	if filter.Result != "" {
		query = query.Where("EXISTS (SELECT 1 FROM scan_observations so WHERE so.batch_id = scan_batches.id AND so.deleted_at IS NULL AND so.result = ?)", filter.Result)
	}
	if filter.AssetID != nil {
		query = query.Where("EXISTS (SELECT 1 FROM scan_targets st WHERE st.batch_id = scan_batches.id AND st.deleted_at IS NULL AND st.caasm_asset_id = ?)", *filter.AssetID)
	}
	if filter.PublicIP != "" {
		query = query.Where("EXISTS (SELECT 1 FROM scan_targets st WHERE st.batch_id = scan_batches.id AND st.deleted_at IS NULL AND st.public_ip = ?)", strings.TrimSpace(filter.PublicIP))
	}
	return query
}

func applyCampaignFilter(query *gorm.DB, filter CampaignListFilter) *gorm.DB {
	if filter.Status != "" {
		switch strings.ToLower(strings.TrimSpace(filter.Status)) {
		case "active":
			query = query.Where(
				"status IN ? OR (status = ? AND running_jobs > 0)",
				[]string{
					string(scanmodel.CampaignStatusQueued),
					string(scanmodel.CampaignStatusDispatching),
					string(scanmodel.CampaignStatusRunning),
					string(scanmodel.CampaignStatusSyncFailed),
				},
				string(scanmodel.CampaignStatusCanceled),
			)
		default:
			query = query.Where("status = ?", filter.Status)
		}
	}
	if filter.Query != "" {
		like := "%" + strings.TrimSpace(filter.Query) + "%"
		query = query.Where("request_id LIKE ?", like)
	}
	if filter.CreatedBy != nil {
		query = query.Where("created_by = ?", *filter.CreatedBy)
	}
	if filter.From != nil {
		query = query.Where("created_at >= ?", *filter.From)
	}
	if filter.To != nil {
		query = query.Where("created_at <= ?", *filter.To)
	}
	return query
}

func applyTargetFilter(query *gorm.DB, filter TargetListFilter) *gorm.DB {
	if filter.BatchID != 0 {
		query = query.Where("batch_id = ?", filter.BatchID)
	}
	if filter.TargetStatus != "" {
		query = query.Where("target_status = ?", filter.TargetStatus)
	}
	if filter.PublicIP != "" {
		query = query.Where("public_ip = ?", strings.TrimSpace(filter.PublicIP))
	}
	if filter.CAASMAssetID != nil {
		query = query.Where("caasm_asset_id = ?", *filter.CAASMAssetID)
	}
	if filter.CAASMPortID != nil {
		query = query.Where("caasm_port_id = ?", *filter.CAASMPortID)
	}
	return query
}

func applyObservationFilter(query *gorm.DB, filter ObservationListFilter) *gorm.DB {
	if filter.BatchID != 0 {
		query = query.Where("batch_id = ?", filter.BatchID)
	}
	if filter.TargetID != nil {
		query = query.Where("target_id = ?", *filter.TargetID)
	}
	if filter.Result != "" {
		query = query.Where("result = ?", filter.Result)
	}
	if filter.CAASMAssetID != nil {
		query = query.Where("caasm_asset_id = ?", *filter.CAASMAssetID)
	}
	if filter.CAASMPortID != nil {
		query = query.Where("caasm_port_id = ?", *filter.CAASMPortID)
	}
	if filter.PublicIP != "" {
		query = query.Where("public_ip = ?", strings.TrimSpace(filter.PublicIP))
	}
	return query
}

func applyPortExposureFilter(query *gorm.DB, filter PortExposureFilter) *gorm.DB {
	result := strings.TrimSpace(filter.Result)
	if result == "" {
		result = string(scanmodel.ObservationResultConfirmedOpen)
	}
	query = query.Where("o.result = ?", result)
	if filter.PublicIP != "" {
		query = query.Where("o.public_ip = ?", strings.TrimSpace(filter.PublicIP))
	}
	if filter.Port != nil {
		query = query.Where("o.port = ?", *filter.Port)
	}
	if filter.Transport != "" {
		query = query.Where("o.transport = ?", strings.TrimSpace(strings.ToLower(filter.Transport)))
	}
	if filter.AssetID != nil {
		query = query.Where("o.caasm_asset_id = ?", *filter.AssetID)
	}
	if filter.CAASMPortID != nil {
		query = query.Where("o.caasm_port_id = ?", *filter.CAASMPortID)
	}
	if filter.Owner != "" {
		query = query.Where("t.owner LIKE ?", "%"+strings.TrimSpace(filter.Owner)+"%")
	}
	if filter.Environment != "" {
		query = query.Where("t.environment = ?", strings.TrimSpace(filter.Environment))
	}
	return query
}

func normalizePagination(page, pageSize int) (int, int) {
	if page <= 0 {
		page = 1
	}
	if pageSize <= 0 || pageSize > maxPageSize {
		pageSize = 20
	}
	return page, pageSize
}

func normalizeCampaign(campaign *scanmodel.Campaign) {
	campaign.RequestID = strings.TrimSpace(campaign.RequestID)
	if campaign.Source == "" {
		campaign.Source = "caasm_ports"
	}
	if campaign.TargetScope == "" {
		campaign.TargetScope = "all_public_ports"
	}
	if campaign.Status == "" {
		campaign.Status = scanmodel.CampaignStatusQueued
	}
	if campaign.Metadata == nil {
		campaign.Metadata = coremodel.JSONB{}
	}
}

func normalizeBatch(batch *scanmodel.Batch) {
	batch.RequestID = strings.TrimSpace(batch.RequestID)
	if batch.Source == "" {
		batch.Source = scanmodel.SourceAssetPlatform
	}
	if batch.Status == "" {
		batch.Status = scanmodel.BatchStatusDraft
	}
	if batch.TriggerType == "" {
		batch.TriggerType = scanmodel.TriggerTypeManual
	}
	if batch.CampaignID == nil {
		batch.PartCount = 1
	}
	if batch.PartCount <= 0 {
		batch.PartCount = 1
	}
	if batch.Metadata == nil {
		batch.Metadata = coremodel.JSONB{}
	}
}

func normalizeTarget(target *scanmodel.Target) {
	target.ScannerAssetID = strings.TrimSpace(target.ScannerAssetID)
	target.PublicIP = strings.TrimSpace(target.PublicIP)
	target.Transport = strings.ToLower(strings.TrimSpace(target.Transport))
	if target.Transport == "" {
		target.Transport = "tcp"
	}
	if target.TargetStatus == "" {
		target.TargetStatus = scanmodel.TargetStatusPending
	}
	if target.Metadata == nil {
		target.Metadata = coremodel.JSONB{}
	}
}

func normalizeObservation(observation *scanmodel.Observation) {
	observation.PublicIP = strings.TrimSpace(observation.PublicIP)
	observation.Transport = strings.ToLower(strings.TrimSpace(observation.Transport))
	if observation.Transport == "" {
		observation.Transport = "tcp"
	}
	if observation.ReasonCode == "" {
		observation.ReasonCode = "unknown"
	}
	if observation.ObservedAt.IsZero() {
		observation.ObservedAt = time.Now()
	}
	if observation.Metadata == nil {
		observation.Metadata = coremodel.JSONB{}
	}
}

func normalizeCallbackReceipt(receipt *scanmodel.CallbackReceipt) {
	receipt.IdempotencyKey = strings.TrimSpace(receipt.IdempotencyKey)
	receipt.PayloadSHA256 = strings.TrimSpace(receipt.PayloadSHA256)
	receipt.HeaderPayloadSHA256 = strings.TrimSpace(receipt.HeaderPayloadSHA256)
	if receipt.Status == "" {
		receipt.Status = scanmodel.CallbackReceiptStatusReceived
	}
	if receipt.ReceivedAt.IsZero() {
		receipt.ReceivedAt = time.Now()
	}
	if receipt.Metadata == nil {
		receipt.Metadata = coremodel.JSONB{}
	}
}

func observationConflictColumns(observation *scanmodel.Observation) []clause.Column {
	if observation.ScannerObservationID != nil && strings.TrimSpace(*observation.ScannerObservationID) != "" {
		return []clause.Column{{Name: "scanner_observation_id"}}
	}
	if observation.ScannerJobID != nil && strings.TrimSpace(*observation.ScannerJobID) != "" {
		return []clause.Column{{Name: "batch_id"}, {Name: "scanner_job_id"}}
	}
	if observation.ScannerTargetID != nil && strings.TrimSpace(*observation.ScannerTargetID) != "" {
		return []clause.Column{{Name: "batch_id"}, {Name: "scanner_target_id"}}
	}
	if observation.BatchID != 0 && observation.PublicIP != "" && observation.Port > 0 && observation.Transport != "" {
		return []clause.Column{{Name: "batch_id"}, {Name: "public_ip"}, {Name: "port"}, {Name: "transport"}}
	}
	return nil
}