package service

import (
	"context"
	"encoding/json"
	"fmt"
	"strings"

	"gorm.io/gorm"

	coremodel "security-center-backend/internal/model"
	caasmmodel "security-center-backend/internal/modules/caasm/model"
	depmodel "security-center-backend/internal/modules/department/model"
	vulnmodel "security-center-backend/internal/modules/vuln/model"
	"security-center-backend/internal/service/directory"
)

type gormEntityStore struct {
	db *gorm.DB
}

type gormEventFeed struct {
	db *gorm.DB
}

type gormEmployeeLookup struct {
	db *gorm.DB
}

type gormSubmitterLookup struct {
	db *gorm.DB
}

type employeeDirectoryRow struct {
	depmodel.Employee
	DepartmentName string `gorm:"column:department_name"`
}

func newGormEntityStore(db *gorm.DB) EntityStore {
	if db == nil {
		return nil
	}
	return &gormEntityStore{db: db}
}

func newGormEventFeed(db *gorm.DB) EventFeed {
	if db == nil {
		return nil
	}
	return &gormEventFeed{db: db}
}

func newGormEmployeeDirectory(db *gorm.DB) EmployeeLookup {
	if db == nil {
		return nil
	}
	return &gormEmployeeLookup{db: db}
}

func newGormSubmitterLookup(db *gorm.DB) SubmitterLookup {
	if db == nil {
		return nil
	}
	return &gormSubmitterLookup{db: db}
}

func (s *gormEntityStore) GetEntityFields(ctx context.Context, entityType string) ([]FieldInfo, error) {
	if s == nil || s.db == nil {
		return []FieldInfo{}, nil
	}
	entityType = strings.ToLower(strings.TrimSpace(entityType))
	model, err := s.modelForType(entityType)
	if err != nil {
		return nil, err
	}

	colTypes, err := s.db.WithContext(ctx).Migrator().ColumnTypes(model)
	if err != nil {
		return nil, err
	}

	out := make([]FieldInfo, 0, len(colTypes))
	for _, ct := range colTypes {
		name := ct.Name()
		dbType := strings.ToLower(ct.DatabaseTypeName())
		fieldType := "string"
		switch {
		case strings.Contains(dbType, "int") || strings.Contains(dbType, "decimal") || strings.Contains(dbType, "numeric") || strings.Contains(dbType, "float") || strings.Contains(dbType, "double"):
			fieldType = "number"
		case strings.Contains(dbType, "bool") || strings.Contains(dbType, "tinyint"):
			fieldType = "boolean"
		case strings.Contains(dbType, "date") || strings.Contains(dbType, "time"):
			fieldType = "datetime"
		case strings.Contains(dbType, "json"):
			fieldType = "json"
		}
		out = append(out, FieldInfo{Name: name, Type: fieldType})
	}
	return out, nil
}

func (s *gormEntityStore) FindMatches(ctx context.Context, entityType string, entityID *uint64, rawConditions interface{}, limit int) ([]map[string]interface{}, error) {
	if s == nil || s.db == nil {
		return []map[string]interface{}{}, nil
	}
	entityType = strings.ToLower(strings.TrimSpace(entityType))
	model, err := s.modelForType(entityType)
	if err != nil {
		return nil, err
	}
	if limit <= 0 || limit > 200 {
		limit = 50
	}

	query := s.db.WithContext(ctx).Model(model)
	if entityID != nil && *entityID > 0 {
		query = query.Where("id = ?", *entityID)
	}
	query, err = s.applyConditions(ctx, entityType, query, rawConditions)
	if err != nil {
		return nil, err
	}

	rows := make([]map[string]interface{}, 0)
	if err := query.Order("id DESC").Limit(limit).Find(&rows).Error; err != nil {
		return nil, err
	}
	return rows, nil
}

func (s *gormEntityStore) FetchEntity(ctx context.Context, entityType string, id uint64) (map[string]interface{}, error) {
	if s == nil || s.db == nil {
		return nil, fmt.Errorf("entity store not configured")
	}
	entityType = strings.ToLower(strings.TrimSpace(entityType))
	model, err := s.modelForType(entityType)
	if err != nil {
		return nil, err
	}

	var row map[string]interface{}
	if err := s.db.WithContext(ctx).Model(model).First(&row, id).Error; err != nil {
		return nil, err
	}
	return row, nil
}

func (s *gormEntityStore) modelForType(entityType string) (interface{}, error) {
	switch entityType {
	case "vulnerability", "vulnerabilities":
		return &vulnmodel.Vulnerability{}, nil
	case "asset", "assets":
		return &caasmmodel.Asset{}, nil
	default:
		return nil, fmt.Errorf("unsupported entity type: %s", entityType)
	}
}

func (s *gormEntityStore) applyConditions(ctx context.Context, entityType string, query *gorm.DB, raw interface{}) (*gorm.DB, error) {
	if raw == nil {
		return query, nil
	}
	allowedFields, err := s.allowedFieldSet(ctx, entityType)
	if err != nil {
		return nil, err
	}

	var cond RuleConditions
	switch v := raw.(type) {
	case map[string]interface{}:
		data, _ := json.Marshal(v)
		_ = json.Unmarshal(data, &cond)
	case []interface{}:
		data, _ := json.Marshal(v)
		_ = json.Unmarshal(data, &cond.Conditions)
	default:
		data, _ := json.Marshal(v)
		_ = json.Unmarshal(data, &cond)
	}
	if cond.Combinator == "" {
		cond.Combinator = "and"
	}
	if strings.ToLower(cond.Combinator) != "and" {
		return nil, fmt.Errorf("unsupported combinator: %s", cond.Combinator)
	}

	for _, c := range cond.Conditions {
		field := strings.TrimSpace(c.Field)
		if field == "" || !isSafeFieldName(field) || !allowedFields[field] {
			return nil, fmt.Errorf("invalid field: %s", c.Field)
		}
		op := strings.ToLower(strings.TrimSpace(c.effectiveOperator()))
		column := fmt.Sprintf("`%s`", field)
		switch op {
		case "eq":
			query = query.Where(column+" = ?", c.Value)
		case "neq":
			query = query.Where(column+" <> ?", c.Value)
		case "contains":
			query = query.Where(column+" LIKE ?", "%"+fmt.Sprint(c.Value)+"%")
		case "gt":
			query = query.Where(column+" > ?", c.Value)
		case "gte":
			query = query.Where(column+" >= ?", c.Value)
		case "lt":
			query = query.Where(column+" < ?", c.Value)
		case "lte":
			query = query.Where(column+" <= ?", c.Value)
		case "in":
			list, ok := c.Value.([]interface{})
			if !ok {
				return nil, fmt.Errorf("in operator requires array value")
			}
			query = query.Where(column+" IN ?", list)
		case "not_in":
			list, ok := c.Value.([]interface{})
			if !ok {
				return nil, fmt.Errorf("not_in operator requires array value")
			}
			query = query.Where(column+" NOT IN ?", list)
		case "is_null":
			query = query.Where(column + " IS NULL")
		case "not_null":
			query = query.Where(column + " IS NOT NULL")
		default:
			return nil, fmt.Errorf("unsupported operator: %s", c.effectiveOperator())
		}
	}
	return query, nil
}

func (s *gormEntityStore) allowedFieldSet(ctx context.Context, entityType string) (map[string]bool, error) {
	fields, err := s.GetEntityFields(ctx, entityType)
	if err != nil {
		return nil, err
	}
	set := make(map[string]bool, len(fields))
	for _, f := range fields {
		set[f.Name] = true
	}
	return set, nil
}

func isSafeFieldName(field string) bool {
	for _, r := range field {
		if !(r == '_' || r >= '0' && r <= '9' || r >= 'a' && r <= 'z' || r >= 'A' && r <= 'Z') {
			return false
		}
	}
	return true
}

func (s *gormEventFeed) ListVulnerabilityCreates(ctx context.Context, lastVulnerabilityID uint64, limit int) ([]VulnerabilityCreateRef, error) {
	if s == nil || s.db == nil {
		return []VulnerabilityCreateRef{}, nil
	}
	if limit <= 0 {
		limit = 200
	}
	var vulns []vulnmodel.Vulnerability
	if err := s.db.WithContext(ctx).
		Model(&vulnmodel.Vulnerability{}).
		Where("id > ?", lastVulnerabilityID).
		Order("id ASC").
		Limit(limit).
		Find(&vulns).Error; err != nil {
		return nil, err
	}
	out := make([]VulnerabilityCreateRef, 0, len(vulns))
	for _, v := range vulns {
		out = append(out, VulnerabilityCreateRef{ID: v.ID})
	}
	return out, nil
}

func (s *gormEventFeed) ListVulnerabilityEvents(ctx context.Context, lastEventID uint64, limit int) ([]VulnerabilityEventRef, error) {
	if s == nil || s.db == nil {
		return []VulnerabilityEventRef{}, nil
	}
	if limit <= 0 {
		limit = 500
	}
	var events []vulnmodel.VulnerabilityEvent
	if err := s.db.WithContext(ctx).
		Model(&vulnmodel.VulnerabilityEvent{}).
		Where("id > ?", lastEventID).
		Where("event_type IN ?", []string{"status_changed", "claimed", "owner_changed"}).
		Order("id ASC").
		Limit(limit).
		Find(&events).Error; err != nil {
		return nil, err
	}
	out := make([]VulnerabilityEventRef, 0, len(events))
	for _, e := range events {
		out = append(out, VulnerabilityEventRef{ID: e.ID, VulnerabilityID: e.VulnerabilityID, EventType: e.EventType})
	}
	return out, nil
}

func (s *gormEventFeed) ListAssetHistoryChanges(ctx context.Context, lastHistoryID uint64, limit int) ([]AssetHistoryRef, error) {
	if s == nil || s.db == nil {
		return []AssetHistoryRef{}, nil
	}
	if limit <= 0 {
		limit = 500
	}
	var history []caasmmodel.AssetHistory
	if err := s.db.WithContext(ctx).
		Model(&caasmmodel.AssetHistory{}).
		Where("id > ?", lastHistoryID).
		Order("id ASC").
		Limit(limit).
		Find(&history).Error; err != nil {
		return nil, err
	}
	out := make([]AssetHistoryRef, 0, len(history))
	for _, h := range history {
		out = append(out, AssetHistoryRef{ID: h.ID, AssetID: h.AssetID, Action: h.Action})
	}
	return out, nil
}

func (d *gormEmployeeLookup) FindByID(ctx context.Context, id uint64) (*directory.Employee, error) {
	if d == nil || d.db == nil {
		return nil, nil
	}
	var emp depmodel.Employee
	if err := d.db.WithContext(ctx).First(&emp, id).Error; err != nil {
		return nil, err
	}
	return &directory.Employee{
		ID:             emp.ID,
		Name:           emp.Name,
		Email:          emp.Email,
		LarkUserID:     emp.LarkUserID,
		DepartmentID:   emp.DepartmentID,
		DepartmentIDs:  []string(emp.DepartmentIDs),
		DepartmentName: emp.DepartmentName,
		Status:         emp.Status,
		AvatarURL:      emp.AvatarURL,
		JobTitle:       emp.JobTitle,
	}, nil
}

func (d *gormEmployeeLookup) ListActive(ctx context.Context) ([]directory.Employee, error) {
	if d == nil || d.db == nil {
		return []directory.Employee{}, nil
	}
	var rows []employeeDirectoryRow
	if err := d.db.WithContext(ctx).
		Table("employees").
		Select("employees.*, departments.name AS department_name").
		Joins("LEFT JOIN departments ON departments.lark_department_id = employees.department_id").
		Where("employees.status = ?", "active").
		Order("employees.name ASC").
		Find(&rows).Error; err != nil {
		return nil, err
	}
	return rowsToDirectoryEmployees(rows), nil
}

func (d *gormEmployeeLookup) ListByDepartment(ctx context.Context, departmentID string) ([]directory.Employee, error) {
	if d == nil || d.db == nil {
		return []directory.Employee{}, nil
	}
	var rows []employeeDirectoryRow
	query := d.db.WithContext(ctx).
		Table("employees").
		Select("employees.*, departments.name AS department_name").
		Joins("LEFT JOIN departments ON departments.lark_department_id = employees.department_id").
		Where("employees.status = ? AND (employees.department_id = ? OR JSON_CONTAINS(employees.department_ids, JSON_QUOTE(?)))", "active", departmentID, departmentID)
	if err := query.Order("employees.name ASC").Find(&rows).Error; err != nil {
		return nil, err
	}
	return rowsToDirectoryEmployees(rows), nil
}

func (s *gormSubmitterLookup) FindEmployeeIDByUserID(ctx context.Context, userID uint64) (uint64, error) {
	if s == nil || s.db == nil {
		return 0, fmt.Errorf("submitter lookup not configured")
	}
	var user coremodel.User
	if err := s.db.WithContext(ctx).Select("employee_id").First(&user, userID).Error; err != nil {
		return 0, err
	}
	if user.EmployeeID == nil || *user.EmployeeID == 0 {
		return 0, fmt.Errorf("user %d has no employee_id", userID)
	}
	return *user.EmployeeID, nil
}

func rowsToDirectoryEmployees(rows []employeeDirectoryRow) []directory.Employee {
	out := make([]directory.Employee, 0, len(rows))
	for _, row := range rows {
		out = append(out, directory.Employee{
			ID:             row.ID,
			Name:           row.Name,
			Email:          row.Email,
			LarkUserID:     row.LarkUserID,
			DepartmentID:   row.DepartmentID,
			DepartmentName: row.DepartmentName,
			DepartmentIDs:  []string(row.DepartmentIDs),
			Status:         row.Status,
			AvatarURL:      row.AvatarURL,
			JobTitle:       row.JobTitle,
		})
	}
	return out
}