vikunja/pkg/modules/migration/csv/csv.go

// Vikunja is a to-do list application to facilitate your life.
// Copyright 2018-present Vikunja and contributors. All rights reserved.
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package csv

import (
	"bytes"
	"cmp"
	"encoding/csv"
	"encoding/json"
	"errors"
	"io"
	"slices"
	"sort"
	"strconv"
	"strings"
	"time"

	"golang.org/x/text/encoding/unicode"
	"golang.org/x/text/transform"

	"code.vikunja.io/api/pkg/config"
	"code.vikunja.io/api/pkg/models"
	"code.vikunja.io/api/pkg/modules/migration"
	"code.vikunja.io/api/pkg/user"
)

// The converter is only set once the import runs in
// the background - the request carries its config as JSON through SetOptions.
type Migrator struct {
	converter *rowConverter
}

// Name returns the name of this migrator
func (m *Migrator) Name() string {
	return "csv"
}

// MaxLabelsPerTask caps the labels one CSV row can give its task.
const MaxLabelsPerTask = 100

// SupportedDelimiters contains all supported CSV delimiters
var SupportedDelimiters = []string{",", ";", "\t", "|"}

// SupportedQuoteChars contains all supported quote characters
var SupportedQuoteChars = []string{"\"", "'"}

// SupportedDateFormats contains common date formats for parsing
var SupportedDateFormats = []string{
	"2006-01-02",                // ISO date
	"2006-01-02T15:04:05",       // ISO datetime
	"2006-01-02T15:04:05Z07:00", // RFC3339
	"2006-01-02T15:04:05-0700",  // ISO with timezone
	"02/01/2006",                // DD/MM/YYYY
	"01/02/2006",                // MM/DD/YYYY
	"02-01-2006",                // DD-MM-YYYY
	"01-02-2006",                // MM-DD-YYYY
	"Jan 2, 2006",               // Month D, YYYY
	"2 Jan 2006",                // D Month YYYY
	"02/01/2006 15:04",          // DD/MM/YYYY HH:MM
	"01/02/2006 15:04",          // MM/DD/YYYY HH:MM
	"2006-01-02 15:04:05",       // MySQL datetime
	"2006/01/02",                // YYYY/MM/DD
	"02.01.2006",                // DD.MM.YYYY (European)
	"02.01.2006 15:04",          // DD.MM.YYYY HH:MM (European)
	time.RFC1123,                // RFC1123
	time.RFC1123Z,               // RFC1123 with numeric zone
	time.RFC822,                 // RFC822
	time.RFC822Z,                // RFC822 with numeric zone
	time.RFC850,                 // RFC850
	time.ANSIC,                  // ANSIC
	time.UnixDate,               // Unix date
}

// TaskAttribute represents a task attribute that can be mapped from CSV
type TaskAttribute string

const (
	AttrTitle       TaskAttribute = "title"
	AttrDescription TaskAttribute = "description"
	AttrDueDate     TaskAttribute = "due_date"
	AttrStartDate   TaskAttribute = "start_date"
	AttrEndDate     TaskAttribute = "end_date"
	AttrDone        TaskAttribute = "done"
	AttrPriority    TaskAttribute = "priority"
	AttrLabels      TaskAttribute = "labels"
	AttrProject     TaskAttribute = "project"
	AttrReminder    TaskAttribute = "reminder"
	AttrIgnore      TaskAttribute = "ignore"
)

// AllTaskAttributes returns all available task attributes for mapping
var AllTaskAttributes = []TaskAttribute{
	AttrTitle,
	AttrDescription,
	AttrDueDate,
	AttrStartDate,
	AttrEndDate,
	AttrDone,
	AttrPriority,
	AttrLabels,
	AttrProject,
	AttrReminder,
	AttrIgnore,
}

// ColumnMapping represents a mapping from a CSV column to a task attribute
type ColumnMapping struct {
	ColumnIndex int           `json:"column_index" doc:"The zero-based index of the CSV column this mapping applies to."`
	ColumnName  string        `json:"column_name" doc:"The header name of the CSV column, for display."`
	Attribute   TaskAttribute `json:"attribute" enum:"title,description,due_date,start_date,end_date,done,priority,labels,project,reminder,ignore" doc:"The task attribute the column maps to. Use \"ignore\" to drop the column."`
}

// DetectionResult contains the auto-detected CSV structure
type DetectionResult struct {
	Columns          []string        `json:"columns" doc:"The detected column header names, in order."`
	Delimiter        string          `json:"delimiter" doc:"The detected field delimiter (one of \",\", \";\", tab, \"|\")."`
	QuoteChar        string          `json:"quote_char" doc:"The detected quote character."`
	DateFormat       string          `json:"date_format" doc:"The detected Go reference date layout used to parse date columns."`
	SuggestedMapping []ColumnMapping `json:"suggested_mapping" doc:"A best-guess column-to-attribute mapping; the client may edit it before previewing or migrating."`
	PreviewRows      [][]string      `json:"preview_rows" doc:"The first few raw rows of the file, for the client to render a preview."`
}

// ImportConfig contains the configuration for CSV import
type ImportConfig struct {
	Delimiter  string          `json:"delimiter" doc:"The field delimiter to parse with. Defaults to comma when empty."`
	QuoteChar  string          `json:"quote_char" doc:"The quote character to parse with."`
	DateFormat string          `json:"date_format" doc:"The Go reference date layout used to parse date columns."`
	SkipRows   int             `json:"skip_rows" doc:"Number of leading rows to skip (e.g. a header row) before importing."`
	Mapping    []ColumnMapping `json:"mapping" doc:"The column-to-attribute mappings that drive the import."`
}

// PreviewTask represents a task preview before import
type PreviewTask struct {
	Title       string   `json:"title"`
	Description string   `json:"description"`
	DueDate     string   `json:"due_date,omitempty"`
	StartDate   string   `json:"start_date,omitempty"`
	EndDate     string   `json:"end_date,omitempty"`
	Done        bool     `json:"done"`
	Priority    int      `json:"priority"`
	Labels      []string `json:"labels,omitempty"`
	Project     string   `json:"project,omitempty"`
}

// PreviewResult contains preview data before import
type PreviewResult struct {
	Tasks     []PreviewTask `json:"tasks" doc:"The first few tasks that would be imported with the given config."`
	TotalRows int           `json:"total_rows" doc:"The total number of data rows in the file."`
}

// toUTF8 decodes a BOM-marked UTF-16 file (Excel's "Unicode Text" export) and drops
// what Postgres text columns reject: NUL bytes and invalid UTF-8.
func toUTF8(data []byte) []byte {
	if decoded, _, err := transform.Bytes(unicode.BOMOverride(transform.Nop), data); err == nil {
		data = decoded
	}
	return bytes.ToValidUTF8(bytes.ReplaceAll(data, []byte{0}, nil), []byte("\uFFFD"))
}

func readCSVFile(file io.ReaderAt, size int64) ([]byte, error) {
	if size == 0 {
		return nil, &migration.ErrFileIsEmpty{}
	}
	if size > maxImportFileBytes() {
		return nil, &migration.ErrNotACSVFile{}
	}

	data := make([]byte, size)
	_, err := file.ReadAt(data, 0)
	if err != nil && !errors.Is(err, io.EOF) {
		return nil, err
	}
	return toUTF8(data), nil
}

// detectDelimiter attempts to auto-detect the CSV delimiter
func detectDelimiter(data []byte) string {
	content := string(data)

	// Count occurrences of each delimiter in the first few lines
	lines := strings.SplitN(content, "\n", 5)
	if len(lines) < 2 {
		return "," // Default to comma
	}

	delimiterCounts := make(map[string]int)
	for _, delim := range SupportedDelimiters {
		count := 0
		for _, line := range lines[:minInt(3, len(lines))] {
			count += strings.Count(line, delim)
		}
		delimiterCounts[delim] = count
	}

	// Find the delimiter with the most consistent count across lines
	bestDelimiter := ","
	maxCount := 0
	for delim, count := range delimiterCounts {
		if count > maxCount {
			maxCount = count
			bestDelimiter = delim
		}
	}

	return bestDelimiter
}

// detectQuoteChar attempts to auto-detect the quote character
func detectQuoteChar(data []byte) string {
	content := string(data)

	doubleQuotes := strings.Count(content, "\"")
	singleQuotes := strings.Count(content, "'")

	if singleQuotes > doubleQuotes {
		return "'"
	}
	return "\""
}

// detectDateFormat attempts to detect the date format from sample data
func detectDateFormat(sampleDates []string) string {
	if len(sampleDates) == 0 {
		return SupportedDateFormats[0] // Default to ISO
	}

	for _, format := range SupportedDateFormats {
		matches := 0
		for _, dateStr := range sampleDates {
			dateStr = strings.TrimSpace(dateStr)
			if dateStr == "" {
				continue
			}
			_, err := time.Parse(format, dateStr)
			if err == nil {
				matches++
			}
		}
		// If most dates match this format, use it
		if matches > 0 && matches >= len(sampleDates)/2 {
			return format
		}
	}

	return SupportedDateFormats[0]
}

// suggestMapping suggests column mappings based on column names
func suggestMapping(columns []string) []ColumnMapping {
	mappings := make([]ColumnMapping, len(columns))

	// Common column name patterns for each attribute
	patterns := map[TaskAttribute][]string{
		AttrTitle:       {"title", "name", "task", "subject", "summary"},
		AttrDescription: {"description", "content", "notes", "details", "body", "text"},
		AttrDueDate:     {"due", "due_date", "duedate", "deadline", "due date"},
		AttrStartDate:   {"start", "start_date", "startdate", "begin", "start date"},
		AttrEndDate:     {"end", "end_date", "enddate", "finish", "end date"},
		AttrDone:        {"done", "completed", "complete", "finished", "status", "is_done"},
		AttrPriority:    {"priority", "importance", "urgent", "prio"},
		AttrLabels:      {"labels", "tags", "categories", "category", "label", "tag"},
		AttrProject:     {"project", "list", "folder", "group", "project_name", "list_name"},
		AttrReminder:    {"reminder", "remind", "alert", "notification"},
	}

	usedAttributes := make(map[TaskAttribute]bool)

	for i, col := range columns {
		colLower := strings.ToLower(strings.TrimSpace(col))
		mappings[i] = ColumnMapping{
			ColumnIndex: i,
			ColumnName:  col,
			Attribute:   AttrIgnore,
		}

		for attr, keywords := range patterns {
			if usedAttributes[attr] && attr != AttrLabels {
				continue // Don't map the same attribute twice (except labels)
			}
			for _, keyword := range keywords {
				if strings.Contains(colLower, keyword) || colLower == keyword {
					mappings[i].Attribute = attr
					usedAttributes[attr] = true
					break
				}
			}
			if mappings[i].Attribute != AttrIgnore {
				break
			}
		}
	}

	return mappings
}

// One lookahead record detects migration.maxcsvrows overflow without retaining it (GHSA-pqf9-h8g4-8gmh).
func parseCSV(data []byte, delimiter string) (headers []string, dataRows [][]string, err error) {
	// Go's csv.Reader only supports double-quote as the quote character.
	// LazyQuotes mode handles most edge cases including unescaped quotes
	// in fields. We intentionally do not replace non-standard quote chars
	// (e.g. single quotes) as that would corrupt apostrophes in text content.
	reader := csv.NewReader(bytes.NewReader(data))

	if len(delimiter) > 0 {
		reader.Comma = rune(delimiter[0])
	}
	reader.FieldsPerRecord = -1 // Allow variable field counts
	reader.LazyQuotes = true
	reader.TrimLeadingSpace = true

	headers, err = reader.Read()
	if err != nil {
		if errors.Is(err, io.EOF) {
			return nil, nil, &migration.ErrFileIsEmpty{}
		}
		return nil, nil, err
	}

	maxRows := config.MigrationMaxCSVRows.GetInt64()
	if maxRows <= 0 {
		maxRows = 100000
	}
	for int64(len(dataRows)) < maxRows {
		record, readErr := reader.Read()
		if readErr != nil {
			if errors.Is(readErr, io.EOF) {
				break
			}
			return nil, nil, readErr
		}
		dataRows = append(dataRows, record)
	}
	if _, readErr := reader.Read(); readErr == nil {
		return nil, nil, &migration.ErrImportRowLimitExceeded{MaxRows: maxRows}
	} else if !errors.Is(readErr, io.EOF) {
		return nil, nil, readErr
	}

	return headers, dataRows, nil
}

// maxImportFileBytes caps the buffer allocated to read an uploaded CSV file
// into memory. It mirrors the server's configured upload size limit so a
// bogus or corrupted size value can't force an unbounded allocation.
func maxImportFileBytes() int64 {
	// #nosec G115 -- configured value won't exceed int64 max in practice.
	return int64(config.GetMaxFileSizeInMBytes()) * 1024 * 1024
}

// DetectCSVStructure analyzes a CSV file and returns detection results
func DetectCSVStructure(file io.ReaderAt, size int64) (*DetectionResult, error) {
	data, err := readCSVFile(file, size)
	if err != nil {
		return nil, err
	}

	// Detect delimiter and quote character
	delimiter := detectDelimiter(data)
	quoteChar := detectQuoteChar(data)

	// Parse CSV
	headers, rows, err := parseCSV(data, delimiter)
	if err != nil {
		var emptyErr *migration.ErrFileIsEmpty
		if errors.As(err, &emptyErr) {
			return nil, err
		}
		var limitErr *migration.ErrImportRowLimitExceeded
		if errors.As(err, &limitErr) {
			return nil, err
		}
		return nil, &migration.ErrNotACSVFile{}
	}

	// Suggest column mappings
	suggestedMapping := suggestMapping(headers)

	// Collect sample dates for format detection
	var sampleDates []string
	for _, mapping := range suggestedMapping {
		if mapping.Attribute == AttrDueDate || mapping.Attribute == AttrStartDate || mapping.Attribute == AttrEndDate {
			for _, row := range rows {
				if mapping.ColumnIndex < len(row) && row[mapping.ColumnIndex] != "" {
					sampleDates = append(sampleDates, row[mapping.ColumnIndex])
					if len(sampleDates) >= 10 {
						break
					}
				}
			}
		}
	}

	dateFormat := detectDateFormat(sampleDates)

	// Get preview rows (first 5)
	previewRows := rows
	if len(previewRows) > 5 {
		previewRows = previewRows[:5]
	}

	return &DetectionResult{
		Columns:          headers,
		Delimiter:        delimiter,
		QuoteChar:        quoteChar,
		DateFormat:       dateFormat,
		SuggestedMapping: suggestedMapping,
		PreviewRows:      previewRows,
	}, nil
}

// PreviewImport generates a preview of the import based on current mapping
func PreviewImport(file io.ReaderAt, size int64, config *ImportConfig) (*PreviewResult, error) {
	rc, err := newRowConverter(config)
	if err != nil {
		return nil, err
	}

	data, err := readCSVFile(file, size)
	if err != nil {
		return nil, err
	}

	_, rows, err := parseCSV(data, config.Delimiter)
	if err != nil {
		var emptyErr *migration.ErrFileIsEmpty
		if errors.As(err, &emptyErr) {
			return nil, err
		}
		var limitErr *migration.ErrImportRowLimitExceeded
		if errors.As(err, &limitErr) {
			return nil, err
		}
		return nil, &migration.ErrNotACSVFile{}
	}

	// Skip rows if configured
	if config.SkipRows > 0 {
		if config.SkipRows >= len(rows) {
			return &PreviewResult{
				Tasks:     []PreviewTask{},
				TotalRows: 0,
			}, nil
		}
		rows = rows[config.SkipRows:]
	}

	result := &PreviewResult{
		Tasks:     make([]PreviewTask, 0, minInt(5, len(rows))),
		TotalRows: len(rows),
	}

	previewCount := minInt(5, len(rows))
	for i := 0; i < previewCount; i++ {
		task, err := rc.previewTask(rows[i], i)
		if err != nil {
			return nil, err
		}
		result.Tasks = append(result.Tasks, task)
	}

	return result, nil
}

type rowConverter struct {
	config *ImportConfig
	// Sorted by column without ignored columns, so a row stops at its last cell.
	mappings []ColumnMapping
}

func newRowConverter(config *ImportConfig) (*rowConverter, error) {
	mappings := make([]ColumnMapping, 0, len(config.Mapping))
	seen := make(map[int]bool, len(config.Mapping))
	for _, mapping := range config.Mapping {
		if seen[mapping.ColumnIndex] {
			return nil, &migration.ErrCSVDuplicateColumnMapping{Column: mapping.ColumnIndex + 1}
		}
		seen[mapping.ColumnIndex] = true
		if mapping.ColumnIndex >= 0 && mapping.Attribute != AttrIgnore {
			mappings = append(mappings, mapping)
		}
	}
	slices.SortStableFunc(mappings, func(a, b ColumnMapping) int { return cmp.Compare(a.ColumnIndex, b.ColumnIndex) })
	return &rowConverter{config: config, mappings: mappings}, nil
}

// fileRow is the 1-based row in the file of the data row at index, counting the header.
func (rc *rowConverter) fileRow(index int) int {
	return max(rc.config.SkipRows, 0) + index + 2
}

func (rc *rowConverter) checkLabels(labels *labelSet, index int) error {
	if len(labels.titles) > MaxLabelsPerTask {
		return &migration.ErrCSVTooManyLabels{Row: rc.fileRow(index), MaxLabels: MaxLabelsPerTask}
	}
	return nil
}

func (rc *rowConverter) previewTask(row []string, index int) (PreviewTask, error) {
	task := PreviewTask{}
	labels := &labelSet{}

	for _, mapping := range rc.mappings {
		if mapping.ColumnIndex >= len(row) {
			break
		}

		value := strings.TrimSpace(row[mapping.ColumnIndex])
		if value == "" {
			continue
		}

		switch mapping.Attribute {
		case AttrTitle:
			task.Title = value
		case AttrDescription:
			task.Description = value
		case AttrDueDate:
			task.DueDate = value
		case AttrStartDate:
			task.StartDate = value
		case AttrEndDate:
			task.EndDate = value
		case AttrDone:
			task.Done = parseBool(value)
		case AttrPriority:
			task.Priority = parsePriority(value)
		case AttrLabels:
			labels.add(value)
		case AttrProject:
			task.Project = value
		case AttrReminder:
			// Reminders are not supported in preview tasks
		case AttrIgnore:
			// Ignored attributes are not processed
		}
	}

	if err := rc.checkLabels(labels, index); err != nil {
		return PreviewTask{}, err
	}
	task.Labels = labels.titles
	return task, nil
}

// parseBool parses various boolean representations
func parseBool(value string) bool {
	lower := strings.ToLower(strings.TrimSpace(value))
	return lower == "true" || lower == "yes" || lower == "1" || lower == "done" || lower == "completed"
}

// parsePriority parses priority value
func parsePriority(value string) int {
	// Try to parse as number
	if p, err := strconv.Atoi(strings.TrimSpace(value)); err == nil {
		// Vikunja uses 0-5 priority (0=unset, 1=low, 5=urgent)
		if p < 0 {
			return 0
		}
		if p > 5 {
			return 5
		}
		return p
	}

	// Try to parse text priority
	lower := strings.ToLower(strings.TrimSpace(value))
	switch {
	case strings.Contains(lower, "urgent") || strings.Contains(lower, "highest"):
		return 5
	case strings.Contains(lower, "high"):
		return 4
	case strings.Contains(lower, "medium") || strings.Contains(lower, "normal"):
		return 3
	case strings.Contains(lower, "lowest"):
		return 1
	case strings.Contains(lower, "low"):
		return 2
	}

	return 0
}

type labelSet struct {
	titles []string
	seen   map[string]bool
}

// Stopping past MaxLabelsPerTask means a huge cell is never split completely.
func (s *labelSet) add(value string) {
	for value != "" && len(s.titles) <= MaxLabelsPerTask {
		var part string
		part, value, _ = strings.Cut(value, ",")
		label := strings.TrimSpace(part)
		if label == "" || s.seen[label] {
			continue
		}
		if s.seen == nil {
			s.seen = map[string]bool{}
		}
		s.seen[label] = true
		s.titles = append(s.titles, label)
	}
}

// parseDate parses a date string with the given format using the configured timezone
func parseDate(value, format string) time.Time {
	if value == "" {
		return time.Time{}
	}

	loc := config.GetTimeZone()
	trimmed := strings.TrimSpace(value)

	// Try the specified format first
	if t, err := time.ParseInLocation(format, trimmed, loc); err == nil {
		return t
	}

	// Try all supported formats as fallback
	for _, f := range SupportedDateFormats {
		if t, err := time.ParseInLocation(f, trimmed, loc); err == nil {
			return t
		}
	}

	return time.Time{}
}

// Migrate imports CSV data into Vikunja
// @Summary Import all tasks from a CSV file
// @Description Imports tasks from a CSV file into Vikunja. Requires a mapping configuration.
// @tags migration
// @Accept multipart/form-data
// @Produce json
// @Security JWTKeyAuth
// @Param import formData file true "The CSV file to import"
// @Param config formData string true "The import configuration JSON"
// @Success 200 {object} models.Message "A message telling you everything was migrated successfully."
// @Failure 400 {object} models.Message "Invalid CSV file or configuration"
// @Failure 500 {object} models.Message "Internal server error"
// @Router /migration/csv/migrate [put]
func (m *Migrator) Migrate(u *user.User, file io.ReaderAt, size int64) error {
	if m.converter == nil {
		return &migration.ErrCSVConfigRequired{}
	}
	projects, err := m.converter.projects(file, size)
	if err != nil {
		return err
	}
	return migration.InsertFromStructure(projects, u)
}

func (m *Migrator) SetOptions(options []byte) error {
	config := &ImportConfig{}
	if err := json.Unmarshal(options, config); err != nil {
		return &migration.ErrInvalidCSVImportConfig{Err: err}
	}
	rc, err := newRowConverter(config)
	if err != nil {
		return err
	}
	m.converter = rc
	return nil
}

func (rc *rowConverter) projects(file io.ReaderAt, size int64) ([]*models.ProjectWithTasksAndBuckets, error) {
	data, err := readCSVFile(file, size)
	if err != nil {
		return nil, err
	}

	_, rows, err := parseCSV(data, rc.config.Delimiter)
	if err != nil {
		var emptyErr *migration.ErrFileIsEmpty
		if errors.As(err, &emptyErr) {
			return nil, err
		}
		var limitErr *migration.ErrImportRowLimitExceeded
		if errors.As(err, &limitErr) {
			return nil, err
		}
		return nil, &migration.ErrNotACSVFile{}
	}

	// Skip rows if configured
	if rc.config.SkipRows > 0 {
		if rc.config.SkipRows >= len(rows) {
			rows = nil
		} else {
			rows = rows[rc.config.SkipRows:]
		}
	}

	if len(rows) == 0 {
		return nil, &migration.ErrFileIsEmpty{}
	}

	return rc.convert(rows)
}

func (rc *rowConverter) hasProjectMapping() bool {
	for _, mapping := range rc.mappings {
		if mapping.Attribute == AttrProject {
			return true
		}
	}
	return false
}

func (rc *rowConverter) projectName(row []string) string {
	name := "Tasks"
	for _, mapping := range rc.mappings {
		if mapping.ColumnIndex >= len(row) {
			break
		}
		if mapping.Attribute != AttrProject {
			continue
		}
		if pn := strings.TrimSpace(row[mapping.ColumnIndex]); pn != "" {
			name = pn
		}
	}
	return name
}

// Counts items while converting so an oversized file fails before everything is built.
func (rc *rowConverter) convert(rows [][]string) ([]*models.ProjectWithTasksAndBuckets, error) {
	var pseudoParentID int64 = 1
	parentProject := &models.ProjectWithTasksAndBuckets{
		Project: models.Project{
			ID:    pseudoParentID,
			Title: "Imported from CSV",
		},
	}
	items := int64(1)

	hasProjectMapping := rc.hasProjectMapping()
	projects := make(map[string]*models.ProjectWithTasksAndBuckets)

	for i, row := range rows {
		task, err := rc.task(row, i)
		if err != nil {
			return nil, err
		}
		items += 1 + int64(len(task.Labels)) + int64(len(task.Reminders))

		// If no project column is mapped, put all tasks directly in the parent project
		if !hasProjectMapping {
			parentProject.Tasks = append(parentProject.Tasks, &models.TaskWithComments{Task: task})
			if err := migration.CheckItemCount(items); err != nil {
				return nil, err
			}
			continue
		}

		projectName := rc.projectName(row)
		if _, exists := projects[projectName]; !exists {
			items++
			projects[projectName] = &models.ProjectWithTasksAndBuckets{
				Project: models.Project{
					ID:              int64(len(projects)+2) + pseudoParentID,
					ParentProjectID: &pseudoParentID,
					Title:           projectName,
				},
			}
		}
		projects[projectName].Tasks = append(projects[projectName].Tasks, &models.TaskWithComments{Task: task})

		if err := migration.CheckItemCount(items); err != nil {
			return nil, err
		}
	}

	if !hasProjectMapping {
		return []*models.ProjectWithTasksAndBuckets{parentProject}, nil
	}

	// If only one project exists, put all tasks directly in the parent project
	if len(projects) == 1 {
		for _, p := range projects {
			parentProject.Tasks = p.Tasks
		}
		return []*models.ProjectWithTasksAndBuckets{parentProject}, nil
	}

	// Multiple projects: create sub-projects under the parent
	result := []*models.ProjectWithTasksAndBuckets{parentProject}
	for _, p := range projects {
		result = append(result, p)
	}

	// Sort projects by title for consistent ordering
	sort.Slice(result[1:], func(i, j int) bool {
		return result[i+1].Title < result[j+1].Title
	})

	return result, nil
}

func (rc *rowConverter) task(row []string, index int) (models.Task, error) {
	task := models.Task{
		ID: int64(index + 1),
	}
	labels := &labelSet{}

	for _, mapping := range rc.mappings {
		if mapping.ColumnIndex >= len(row) {
			break
		}

		value := strings.TrimSpace(row[mapping.ColumnIndex])
		if value == "" {
			continue
		}

		switch mapping.Attribute {
		case AttrTitle:
			task.Title = value
		case AttrDescription:
			task.Description = value
		case AttrDueDate:
			task.DueDate = parseDate(value, rc.config.DateFormat)
		case AttrStartDate:
			task.StartDate = parseDate(value, rc.config.DateFormat)
		case AttrEndDate:
			task.EndDate = parseDate(value, rc.config.DateFormat)
		case AttrDone:
			task.Done = parseBool(value)
			if task.Done {
				task.DoneAt = time.Now()
			}
		case AttrPriority:
			task.Priority = int64(parsePriority(value))
		case AttrLabels:
			labels.add(value)
		case AttrReminder:
			// Parse reminder as duration or date
			reminderDate := parseDate(value, rc.config.DateFormat)
			if !reminderDate.IsZero() {
				task.Reminders = []*models.TaskReminder{
					{
						Reminder: reminderDate,
					},
				}
			}
		case AttrProject:
			// Project attribute is handled separately for task creation
		case AttrIgnore:
			// Ignored attributes are not processed
		}
	}

	if err := rc.checkLabels(labels, index); err != nil {
		return models.Task{}, err
	}
	for _, title := range labels.titles {
		task.Labels = append(task.Labels, &models.Label{Title: title})
	}

	// Ensure task has a title
	if task.Title == "" {
		task.Title = "Untitled Task"
	}

	return task, nil
}

func minInt(a, b int) int {
	if a < b {
		return a
	}
	return b
}