package repository

import (
	"context"
	"fmt"
	"strings"
	"time"

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

	vulnmodel "security-center-backend/internal/modules/vuln/model"
)

type ListParams struct {
	Page               int
	PageSize           int
	Statuses           []vulnmodel.VulnerabilityStatus
	Severities         []vulnmodel.SeverityLevel
	ActorUserID        *uint64
	ActorEmployeeID    *uint64
	ActorCanViewFixed  bool
	RestrictVisibility bool
	OwnerEmployeeID    *uint64
	SubmitterUserID    *uint64
	Application        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
	FindByID(ctx context.Context, id uint64) (*vulnmodel.Vulnerability, error)
	FindByPublicID(ctx context.Context, publicID string) (*vulnmodel.Vulnerability, error)
	Update(ctx context.Context, vuln *vulnmodel.Vulnerability) error
	UpdateFields(ctx context.Context, id uint64, fields map[string]interface{}) 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{}).
		Select([]string{
			"id",
			"public_id",
			"title",
			"severity_level",
			"status",
			"application",
			"code_repository",
			"vulnerability_type",
			"discovery_channel",
			"discovery_phase",
			"network_env",
			"is_third_party",
			"interface_path",
			"source",
			"affected_assets_count",
			"tags",
			"has_poc_verification",
			"dependency_name",
			"dependency_version",
			"batch_group_id",
			"discoverer_employee_id",
			"fixer_employee_id",
			"owner_employee_id",
			"authorized_viewer_employee_ids",
			"is_public_viewable",
			"submitter_user_id",
			"department_id",
			"submitted_at",
			"sla_deadline",
			"is_overdue",
			"created_at",
			"updated_at",
		}).
		Preload("Owner", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Submitter", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url")
		}).
		Preload("Discoverer", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Fixer", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Department")

	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)
	}
	query = applyRestrictedVisibility(query, params)
	if params.OwnerEmployeeID != nil {
		query = query.Where("owner_employee_id = ?", *params.OwnerEmployeeID)
	}
	if params.SubmitterUserID != nil {
		query = query.Where("submitter_user_id = ?", *params.SubmitterUserID)
	}
	if params.Application != "" {
		query = query.Where("application = ?", params.Application)
	}
	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":                {},
		"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 applyRestrictedVisibility(query *gorm.DB, params ListParams) *gorm.DB {
	if !params.RestrictVisibility {
		return query
	}
	if params.ActorEmployeeID != nil {
		submitterUserID := uint64(0)
		if params.ActorUserID != nil {
			submitterUserID = *params.ActorUserID
		}
		visibility := query.Session(&gorm.Session{NewDB: true}).
			Where("is_public_viewable = ?", true).
			Or("submitter_user_id = ?", submitterUserID).
			Or("fixer_employee_id = ?", *params.ActorEmployeeID).
			Or("owner_employee_id = ?", *params.ActorEmployeeID).
			Or("JSON_CONTAINS(authorized_viewer_employee_ids, JSON_ARRAY(?))", *params.ActorEmployeeID)
		if params.ActorCanViewFixed {
			visibility = visibility.Or("status = ?", vulnmodel.StatusFixed)
		}
		return query.Where(visibility)
	}
	if params.ActorUserID != nil {
		visibility := query.Session(&gorm.Session{NewDB: true}).
			Where("is_public_viewable = ?", true).
			Or("submitter_user_id = ?", *params.ActorUserID)
		if params.ActorCanViewFixed {
			visibility = visibility.Or("status = ?", vulnmodel.StatusFixed)
		}
		return query.Where(visibility)
	}
	return query.Where("is_public_viewable = ?", true)
}

func (r *GormVulnerabilityRepository) Create(ctx context.Context, vuln *vulnmodel.Vulnerability) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if strings.TrimSpace(vuln.PublicID) == "" {
			publicID, err := allocateVulnerabilityPublicID(tx, vuln.SubmittedAt)
			if err != nil {
				return err
			}
			vuln.PublicID = publicID
		}
		return tx.Create(vuln).Error
	})
}

func (r *GormVulnerabilityRepository) withDetailPreloads(ctx context.Context) *gorm.DB {
	return r.db.WithContext(ctx).
		Model(&vulnmodel.Vulnerability{}).
		Preload("Owner", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Submitter", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url")
		}).
		Preload("Discoverer", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Fixer", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "email", "avatar_url", "department_id")
		}).
		Preload("Department")
}

func (r *GormVulnerabilityRepository) FindByID(ctx context.Context, id uint64) (*vulnmodel.Vulnerability, error) {
	var vuln vulnmodel.Vulnerability
	err := r.withDetailPreloads(ctx).First(&vuln, id).Error
	if err != nil {
		return nil, err
	}
	return &vuln, nil
}

func (r *GormVulnerabilityRepository) FindByPublicID(ctx context.Context, publicID string) (*vulnmodel.Vulnerability, error) {
	publicID = strings.TrimSpace(publicID)
	if publicID == "" {
		return nil, gorm.ErrRecordNotFound
	}

	var vuln vulnmodel.Vulnerability
	err := r.withDetailPreloads(ctx).Where("public_id = ?", publicID).First(&vuln).Error
	if err != nil {
		return nil, err
	}
	return &vuln, nil
}

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

func (r *GormVulnerabilityRepository) UpdateFields(ctx context.Context, id uint64, fields map[string]interface{}) error {
	return r.db.WithContext(ctx).Model(&vulnmodel.Vulnerability{}).Where("id = ?", id).Updates(fields).Error
}

func allocateVulnerabilityPublicID(tx *gorm.DB, submittedAt time.Time) (string, error) {
	ts := submittedAt.UTC()
	if ts.IsZero() {
		ts = time.Now().UTC()
	}
	sequenceMonth := ts.Format("2006-01")
	publicPrefix := ts.Format("06-01")
	if err := tx.Exec(`
		INSERT INTO vulnerability_public_id_sequences (sequence_month, `+"`last_value`"+`)
		VALUES (?, 1)
		ON DUPLICATE KEY UPDATE
			`+"`last_value`"+` = LAST_INSERT_ID(`+"`last_value`"+` + 1),
			updated_at = CURRENT_TIMESTAMP
	`, sequenceMonth).Error; err != nil {
		return "", fmt.Errorf("allocate vulnerability public id: %w", err)
	}
	var nextValue uint64
	if err := tx.Raw("SELECT LAST_INSERT_ID()").Scan(&nextValue).Error; err != nil {
		return "", fmt.Errorf("read vulnerability public id sequence: %w", err)
	}
	if nextValue == 0 {
		nextValue = 1
	}
	return fmt.Sprintf("vul-%s-%04d", publicPrefix, nextValue), nil
}