package repository

import (
	"context"
	"strings"
	"time"

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

	vulnmodel "vul-backend/internal/modules/vuln/model"
)

type ListParams struct {
	Page              int
	PageSize          int
	Statuses          []vulnmodel.VulnerabilityStatus
	Severities        []vulnmodel.SeverityLevel
	OwnerID           *uint64
	SubmitterID       *uint64
	Application       string
	BusinessDept      string
	DiscoveryPhase    string
	DiscoveryChannel  string
	NetworkEnv        string
	HasPOC            *bool
	IsOverdue         *bool
	IsThirdParty      *bool
	Tags              []string
	DependencyName    string
	DependencyVersion string
	BatchGroupID      *uint64
	HasBatchGroup     *bool
	Keyword           string
	SubmittedFrom     *time.Time
	SubmittedTo       *time.Time
	SortBy            string
	SortOrder         string
}

type VulnerabilityRepository interface {
	List(ctx context.Context, params ListParams) ([]vulnmodel.Vulnerability, int64, error)
	Create(ctx context.Context, vuln *vulnmodel.Vulnerability) error
}

type GormVulnerabilityRepository struct {
	db *gorm.DB
}

func NewVulnerabilityRepository(db *gorm.DB) VulnerabilityRepository {
	return &GormVulnerabilityRepository{db: db}
}

func (r *GormVulnerabilityRepository) List(ctx context.Context, params ListParams) ([]vulnmodel.Vulnerability, int64, error) {
	page := params.Page
	if page <= 0 {
		page = 1
	}
	pageSize := params.PageSize
	if pageSize <= 0 || pageSize > 200 {
		pageSize = 20
	}

	var (
		items []vulnmodel.Vulnerability
		total int64
	)

	query := r.db.WithContext(ctx).
		Model(&vulnmodel.Vulnerability{}).
		Preload("Owner", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url")
		}).
		Preload("Submitter", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url")
		})

	if len(params.Statuses) > 0 {
		query = query.Where("status IN ?", params.Statuses)
	}
	if len(params.Severities) > 0 {
		query = query.Where("severity_level IN ?", params.Severities)
	}
	if params.OwnerID != nil {
		query = query.Where("owner_id = ?", *params.OwnerID)
	}
	if params.SubmitterID != nil {
		query = query.Where("submitter_id = ?", *params.SubmitterID)
	}
	if params.Application != "" {
		query = query.Where("application = ?", params.Application)
	}
	if params.BusinessDept != "" {
		query = query.Where("business_dept = ?", params.BusinessDept)
	}
	if params.DiscoveryPhase != "" {
		query = query.Where("discovery_phase = ?", params.DiscoveryPhase)
	}
	if params.DiscoveryChannel != "" {
		query = query.Where("discovery_channel = ?", params.DiscoveryChannel)
	}
	if params.NetworkEnv != "" {
		query = query.Where("network_env = ?", params.NetworkEnv)
	}
	if params.HasPOC != nil {
		query = query.Where("has_poc_verification = ?", *params.HasPOC)
	}
	if params.IsOverdue != nil {
		query = query.Where("is_overdue = ?", *params.IsOverdue)
	}
	if params.IsThirdParty != nil {
		query = query.Where("is_third_party = ?", *params.IsThirdParty)
	}
	if params.DependencyName != "" {
		query = query.Where("dependency_name = ?", params.DependencyName)
	}
	if params.DependencyVersion != "" {
		query = query.Where("dependency_version = ?", params.DependencyVersion)
	}
	if params.BatchGroupID != nil {
		query = query.Where("batch_group_id = ?", *params.BatchGroupID)
	}
	if params.HasBatchGroup != nil {
		if *params.HasBatchGroup {
			query = query.Where("batch_group_id IS NOT NULL")
		} else {
			query = query.Where("batch_group_id IS NULL")
		}
	}
	if params.SubmittedFrom != nil {
		query = query.Where("submitted_at >= ?", params.SubmittedFrom)
	}
	if params.SubmittedTo != nil {
		query = query.Where("submitted_at <= ?", params.SubmittedTo)
	}
	if params.Keyword != "" {
		like := "%" + params.Keyword + "%"
		query = query.Where(
			r.db.
				Where("title LIKE ?", like).
				Or("description LIKE ?", like).
				Or("dependency_name LIKE ?", like).
				Or("source LIKE ?", like),
		)
	}
	if len(params.Tags) > 0 {
		for _, tag := range params.Tags {
			trimmed := strings.TrimSpace(tag)
			if trimmed == "" {
				continue
			}
			query = query.Where("JSON_CONTAINS(tags, JSON_QUOTE(?))", trimmed)
		}
	}

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

	sortableColumns := map[string]struct{}{
		"submitted_at":          {},
		"sla_deadline":          {},
		"severity_level":        {},
		"status":                {},
		"business_dept":         {},
		"application":           {},
		"affected_assets_count": {},
	}
	sortBy := "submitted_at"
	if _, ok := sortableColumns[params.SortBy]; ok {
		sortBy = params.SortBy
	}
	sortOrderDesc := true
	if strings.EqualFold(params.SortOrder, "asc") {
		sortOrderDesc = false
	}

	orderClause := clause.OrderByColumn{
		Column: clause.Column{Name: sortBy},
		Desc:   sortOrderDesc,
	}

	if err := query.
		Order(orderClause).
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&items).Error; err != nil {
		return nil, 0, err
	}

	return items, total, nil
}

func (r *GormVulnerabilityRepository) Create(ctx context.Context, vuln *vulnmodel.Vulnerability) error {
	return r.db.WithContext(ctx).Create(vuln).Error
}