package controllers

import (
	"encoding/json"
	"fmt"
	"io"
	"net/http"
	"strings"
	"time"

	"github.com/readysite/readysite/pkg/application"
	"github.com/readysite/readysite/website/internal/access"
	"github.com/readysite/readysite/website/internal/content"
	"github.com/readysite/readysite/website/internal/helpers"
	"github.com/readysite/readysite/website/models"
)

// Collections returns the collections controller.
func Collections() (string, *CollectionsController) {
	return "collections", &CollectionsController{}
}

// CollectionsController handles collection and document CRUD operations.
type CollectionsController struct {
	application.BaseController
}

// Setup registers routes.
func (c *CollectionsController) Setup(app *application.App) {
	c.BaseController.Setup(app)

	// Export, backup, and import routes (used by React admin for file downloads)
	http.Handle("GET /admin/collections/{id}/backup", app.Method(c, "Backup", RequireAuth))
	http.Handle("GET /admin/collections/{id}/export", app.Method(c, "Export", RequireAuth))
	http.Handle("POST /admin/collections/{id}/import", app.Method(c, "Import", RequireAuth))
}

// Handle implements Controller interface with value receiver for request isolation.
func (c CollectionsController) Handle(r *http.Request) application.Controller {
	c.Request = r
	return &c
}

// Collection returns the current collection from path parameter.
func (c *CollectionsController) Collection() *models.Collection {
	if c.Request == nil {
		return nil
	}
	id := c.PathValue("id")
	if id == "" {
		return nil
	}
	collection, err := models.Collections.Get(id)
	if err != nil {
		return nil
	}
	return collection
}

// Collections returns all collections.
func (c *CollectionsController) Collections() []*models.Collection {
	collections, _ := models.Collections.Search("ORDER BY Name")
	return collections
}

// Document returns the current document from path parameter.
func (c *CollectionsController) Document() *models.Document {
	if c.Request == nil {
		return nil
	}
	docID := c.PathValue("doc")
	if docID == "" {
		return nil
	}
	doc, err := models.Documents.Get(docID)
	if err != nil {
		return nil
	}
	return doc
}

// DocumentPagination returns pagination state for documents list.
func (c *CollectionsController) DocumentPagination() *helpers.Pagination {
	col := c.Collection()
	if col == nil {
		return helpers.NewPagination(c.Request, 0)
	}
	total := col.DocumentCount()
	return helpers.NewPagination(c.Request, total)
}

// Documents returns paginated documents for the current collection.
func (c *CollectionsController) Documents() []*models.Document {
	col := c.Collection()
	if col == nil {
		return nil
	}
	p := c.DocumentPagination()
	docs, _ := models.Documents.Search("WHERE CollectionID = ? ORDER BY CreatedAt DESC LIMIT ? OFFSET ?",
		col.ID, p.PageSize, p.Offset)
	return docs
}

// SchemaFields returns the parsed schema fields for the current collection.
func (c *CollectionsController) SchemaFields() []content.Field {
	col := c.Collection()
	if col == nil {
		return nil
	}
	fields, _ := content.GetFields(col)
	return fields
}

// IsNewCollection returns true if creating a new collection.
func (c *CollectionsController) IsNewCollection() bool {
	if c.Request == nil {
		return true
	}
	id := c.PathValue("id")
	return id == ""
}

// IsNewDocument returns true if creating a new document.
func (c *CollectionsController) IsNewDocument() bool {
	if c.Request == nil {
		return true
	}
	docID := c.PathValue("doc")
	return docID == ""
}

// FieldTypes returns available field types for schema builder.
func (c *CollectionsController) FieldTypes() []string {
	return []string{
		content.Text,
		content.Number,
		content.Bool,
		content.Date,
		content.Email,
		content.URL,
		content.Select,
		content.Relation,
		content.File,
		content.JSON,
	}
}

// Create creates a new collection.
func (c *CollectionsController) Create(w http.ResponseWriter, r *http.Request) {
	slug := r.FormValue("slug")
	name := r.FormValue("name")
	description := r.FormValue("description")
	schema := r.FormValue("schema")

	// Validate slug if provided
	if err := content.ValidateSlug(slug); err != nil {
		c.RenderError(w, r, err)
		return
	}
	if err := content.CheckSlugAvailable(slug, "collection"); err != nil {
		c.RenderError(w, r, err)
		return
	}

	// Validate collection input fields
	if err := content.ValidateCollectionInput(name, description, schema); err != nil {
		c.RenderError(w, r, err)
		return
	}

	collection := &models.Collection{
		Name:        name,
		Description: description,
		Schema:      schema,
	}

	// Use slug as ID if provided
	if slug != "" {
		collection.ID = slug
	}

	id, err := models.Collections.Insert(collection)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to create collection: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	helpers.AuditCreate(r, userID, helpers.ResourceCollection, id, collection.Name)

	w.Header().Set("HX-Trigger", `{"showToast":"Collection created"}`)
	c.Refresh(w, r)
}

// Update updates an existing collection.
func (c *CollectionsController) Update(w http.ResponseWriter, r *http.Request) {
	id := r.PathValue("id")
	name := r.FormValue("name")
	description := r.FormValue("description")
	schema := r.FormValue("schema")

	// Validate collection input fields
	if err := content.ValidateCollectionInput(name, description, schema); err != nil {
		c.RenderError(w, r, err)
		return
	}

	collection, err := models.Collections.Get(id)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Check for concurrent edit conflict using UpdatedAt
	expectedVersion := r.FormValue("version")
	if expectedVersion != "" && collection.UpdatedAt.Format("2006-01-02T15:04:05") != expectedVersion {
		c.RenderError(w, r, fmt.Errorf("This collection was modified by another user. Please reload and try again."))
		return
	}

	collection.Name = name
	collection.Description = description
	collection.Schema = schema

	if err := models.Collections.Update(collection); err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to update collection: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	helpers.AuditUpdate(r, userID, helpers.ResourceCollection, id, collection.Name, nil)

	w.Header().Set("HX-Trigger", `{"showToast":"Collection saved"}`)
	c.Refresh(w, r)
}

// Delete deletes a collection and all its documents.
func (c *CollectionsController) Delete(w http.ResponseWriter, r *http.Request) {
	id := r.PathValue("id")
	collection, err := models.Collections.Get(id)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Delete all documents first
	docs, _ := collection.Documents()
	for _, doc := range docs {
		models.Documents.Delete(doc)
	}

	if err := models.Collections.Delete(collection); err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to delete collection: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	helpers.AuditDelete(r, userID, helpers.ResourceCollection, id, collection.Name)

	c.Redirect(w, r, "/admin/collections")
}

// CreateDocument creates a new document.
func (c *CollectionsController) CreateDocument(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Check authorization - user must have write permission on collection
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermWrite) {
		c.RenderError(w, r, fmt.Errorf("You don't have permission to add documents to this collection"))
		return
	}

	// Parse form data into JSON
	data, err := content.ParseFormData(collection, r)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to parse form data: %w", err))
		return
	}

	// Validate document against schema
	if err := content.ValidateDocument(collection, data); err != nil {
		c.RenderError(w, r, fmt.Errorf("Validation failed: %w", err))
		return
	}

	jsonData, err := json.Marshal(data)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to encode document: %w", err))
		return
	}

	doc := &models.Document{
		CollectionID: collectionID,
		Data:         string(jsonData),
	}

	docID, err := models.Documents.Insert(doc)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to create document: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	helpers.AuditCreate(r, userID, helpers.ResourceDocument, docID, collection.Name+" document")

	// Check for redirect URL template (for public pages like blog-new)
	// Replace {{id}} with the new document ID
	if redirectURL := r.Header.Get("X-Redirect-URL"); redirectURL != "" {
		redirectURL = strings.ReplaceAll(redirectURL, "{{id}}", docID)
		w.Header().Set("HX-Redirect", redirectURL)
		return
	}

	w.Header().Set("HX-Trigger", `{"showToast":"Document created"}`)
	c.Refresh(w, r)
}

// UpdateDocument updates an existing document.
func (c *CollectionsController) UpdateDocument(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	docID := r.PathValue("doc")

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Check authorization - user must have write permission on collection
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermWrite) {
		c.RenderError(w, r, fmt.Errorf("You don't have permission to edit documents in this collection"))
		return
	}

	doc, err := models.Documents.Get(docID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Document not found"))
		return
	}

	// Check for concurrent edit conflict using UpdatedAt
	expectedVersion := r.FormValue("version")
	if expectedVersion != "" && doc.UpdatedAt.Format("2006-01-02T15:04:05") != expectedVersion {
		c.RenderError(w, r, fmt.Errorf("This document was modified by another user. Please reload and try again."))
		return
	}

	// Parse form data into JSON
	data, err := content.ParseFormData(collection, r)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to parse form data: %w", err))
		return
	}

	// Validate document against schema
	if err := content.ValidateDocument(collection, data); err != nil {
		c.RenderError(w, r, fmt.Errorf("Validation failed: %w", err))
		return
	}

	jsonData, err := json.Marshal(data)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to encode document: %w", err))
		return
	}
	doc.Data = string(jsonData)

	if err := models.Documents.Update(doc); err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to update document: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	helpers.AuditUpdate(r, userID, helpers.ResourceDocument, docID, collection.Name+" document", nil)

	// Check for redirect URL (for public pages like blog-edit)
	if redirectURL := r.Header.Get("X-Redirect-URL"); redirectURL != "" {
		redirectURL = strings.ReplaceAll(redirectURL, "{{id}}", docID)
		w.Header().Set("HX-Redirect", redirectURL)
		return
	}

	w.Header().Set("HX-Trigger", `{"showToast":"Document saved"}`)
	c.Refresh(w, r)
}

// DeleteDocument deletes a document.
func (c *CollectionsController) DeleteDocument(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	docID := r.PathValue("doc")

	// Check authorization - user must have delete permission on collection
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermDelete) {
		c.RenderError(w, r, fmt.Errorf("You don't have permission to delete documents from this collection"))
		return
	}

	doc, err := models.Documents.Get(docID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Document not found"))
		return
	}

	if err := models.Documents.Delete(doc); err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to delete document: %w", err))
		return
	}

	// Audit log
	userID := ""
	if user := access.GetUserFromJWT(r); user != nil {
		userID = user.ID
	}
	collection, _ := models.Collections.Get(collectionID)
	collectionName := collectionID
	if collection != nil {
		collectionName = collection.Name
	}
	helpers.AuditDelete(r, userID, helpers.ResourceDocument, docID, collectionName+" document")

	// Check for redirect URL (for public pages like blog-edit)
	if redirectURL := r.Header.Get("X-Redirect-URL"); redirectURL != "" {
		w.Header().Set("HX-Redirect", redirectURL)
		return
	}

	c.Redirect(w, r, "/admin/collections/"+collectionID+"/documents")
}

// --- Backup and Export ---

// CollectionBackup represents a backup of a collection.
type CollectionBackup struct {
	Version    string         `json:"version"`
	ExportedAt string         `json:"exportedAt"`
	Collection CollectionMeta `json:"collection"`
	Documents  []DocumentData `json:"documents"`
}

// CollectionMeta holds collection metadata for backup.
type CollectionMeta struct {
	ID          string `json:"id"`
	Name        string `json:"name"`
	Description string `json:"description"`
	Type        string `json:"type"`
	Schema      string `json:"schema"`
	Query       string `json:"query,omitempty"`
	ListRule    string `json:"listRule,omitempty"`
	ViewRule    string `json:"viewRule,omitempty"`
	CreateRule  string `json:"createRule,omitempty"`
	UpdateRule  string `json:"updateRule,omitempty"`
	DeleteRule  string `json:"deleteRule,omitempty"`
}

// DocumentData holds document data for backup/export.
type DocumentData struct {
	ID        string         `json:"id"`
	Data      map[string]any `json:"data"`
	CreatedAt string         `json:"created"`
	UpdatedAt string         `json:"updated"`
}

// Backup downloads a collection backup as JSON.
// GET /admin/collections/{id}/backup
func (c *CollectionsController) Backup(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")

	// Check authorization
	user := access.GetUserFromJWT(r)
	if !access.IsAdmin(user) {
		c.RenderError(w, r, fmt.Errorf("Admin access required for backup"))
		return
	}

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Get all documents
	docs, err := models.Documents.Search("WHERE CollectionID = ?", collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to fetch documents: %w", err))
		return
	}

	// Build backup structure
	backup := CollectionBackup{
		Version:    "1.0",
		ExportedAt: time.Now().Format("2006-01-02T15:04:05Z"),
		Collection: CollectionMeta{
			ID:          collection.ID,
			Name:        collection.Name,
			Description: collection.Description,
			Type:        collection.Type,
			Schema:      collection.Schema,
			Query:       collection.Query,
			ListRule:    collection.ListRule,
			ViewRule:    collection.ViewRule,
			CreateRule:  collection.CreateRule,
			UpdateRule:  collection.UpdateRule,
			DeleteRule:  collection.DeleteRule,
		},
		Documents: make([]DocumentData, 0, len(docs)),
	}

	for _, doc := range docs {
		var data map[string]any
		if doc.Data != "" {
			json.Unmarshal([]byte(doc.Data), &data)
		}
		backup.Documents = append(backup.Documents, DocumentData{
			ID:        doc.ID,
			Data:      data,
			CreatedAt: doc.CreatedAt.Format("2006-01-02T15:04:05Z"),
			UpdatedAt: doc.UpdatedAt.Format("2006-01-02T15:04:05Z"),
		})
	}

	// Set response headers for download
	filename := fmt.Sprintf("%s-backup-%s.json", collection.ID, time.Now().Format("2006-01-02"))
	w.Header().Set("Content-Type", "application/json")
	w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%q", filename))

	// Audit log
	helpers.AuditRead(r, user.ID, helpers.ResourceCollection, collectionID, collection.Name+" backup")

	// Encode and send
	encoder := json.NewEncoder(w)
	encoder.SetIndent("", "  ")
	encoder.Encode(backup)
}

// Restore restores a collection from a backup.
// POST /admin/collections/{id}/restore
// Query param: mode=replace|merge|append (default: merge)
func (c *CollectionsController) Restore(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	mode := r.URL.Query().Get("mode")
	if mode == "" {
		mode = "merge"
	}

	// Check authorization
	user := access.GetUserFromJWT(r)
	if !access.IsAdmin(user) {
		c.RenderError(w, r, fmt.Errorf("Admin access required for restore"))
		return
	}

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Parse uploaded file
	r.ParseMultipartForm(50 << 20) // 50MB max
	file, _, err := r.FormFile("file")
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("No backup file provided"))
		return
	}
	defer file.Close()

	// Decode backup
	var backup CollectionBackup
	if err := json.NewDecoder(file).Decode(&backup); err != nil {
		c.RenderError(w, r, fmt.Errorf("Invalid backup file: %w", err))
		return
	}

	// Apply based on mode
	var inserted, updated, skipped int

	switch mode {
	case "replace":
		// Delete all existing documents first
		existing, _ := models.Documents.Search("WHERE CollectionID = ?", collectionID)
		for _, doc := range existing {
			models.Documents.Delete(doc)
		}
		// Insert all from backup
		for _, docData := range backup.Documents {
			doc := &models.Document{
				CollectionID: collectionID,
			}
			dataBytes, _ := json.Marshal(docData.Data)
			doc.Data = string(dataBytes)
			models.Documents.Insert(doc)
			inserted++
		}

	case "merge":
		// Update existing by ID, insert new
		for _, docData := range backup.Documents {
			existing, _ := models.Documents.Get(docData.ID)
			if existing != nil && existing.CollectionID == collectionID {
				// Update existing
				dataBytes, _ := json.Marshal(docData.Data)
				existing.Data = string(dataBytes)
				models.Documents.Update(existing)
				updated++
			} else {
				// Insert new
				doc := &models.Document{
					CollectionID: collectionID,
				}
				dataBytes, _ := json.Marshal(docData.Data)
				doc.Data = string(dataBytes)
				models.Documents.Insert(doc)
				inserted++
			}
		}

	case "append":
		// Insert all with new IDs
		for _, docData := range backup.Documents {
			doc := &models.Document{
				CollectionID: collectionID,
			}
			dataBytes, _ := json.Marshal(docData.Data)
			doc.Data = string(dataBytes)
			models.Documents.Insert(doc)
			inserted++
		}

	default:
		c.RenderError(w, r, fmt.Errorf("Invalid restore mode: %s", mode))
		return
	}

	// Audit log
	helpers.AuditUpdate(r, user.ID, helpers.ResourceCollection, collectionID,
		fmt.Sprintf("%s restore (%s mode): %d inserted, %d updated, %d skipped",
			collection.Name, mode, inserted, updated, skipped), nil)

	w.Header().Set("HX-Trigger", fmt.Sprintf(`{"showToast":"Restore complete: %d inserted, %d updated"}`, inserted, updated))
	c.Refresh(w, r)
}

// Export exports collection documents as JSON or CSV.
// GET /admin/collections/{id}/export?format=json|csv
func (c *CollectionsController) Export(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	format := r.URL.Query().Get("format")
	if format == "" {
		format = "json"
	}

	// Check authorization
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermRead) {
		c.RenderError(w, r, fmt.Errorf("Access denied"))
		return
	}

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Get documents
	docs, err := models.Documents.Search("WHERE CollectionID = ?", collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Failed to fetch documents: %w", err))
		return
	}

	filename := fmt.Sprintf("%s-export-%s.%s", collection.ID, time.Now().Format("2006-01-02"), format)

	// Audit log
	userID := ""
	if user != nil {
		userID = user.ID
	}
	helpers.AuditRead(r, userID, helpers.ResourceCollection, collectionID, collection.Name+" export ("+format+")")

	switch format {
	case "json":
		c.exportJSON(w, collection, docs, filename)
	case "csv":
		c.exportCSV(w, collection, docs, filename)
	default:
		c.RenderError(w, r, fmt.Errorf("Unsupported format: %s", format))
	}
}

func (c *CollectionsController) exportJSON(w http.ResponseWriter, collection *models.Collection, docs []*models.Document, filename string) {
	items := make([]map[string]any, 0, len(docs))
	for _, doc := range docs {
		item := map[string]any{
			"id":      doc.ID,
			"created": doc.CreatedAt.Format("2006-01-02T15:04:05Z"),
			"updated": doc.UpdatedAt.Format("2006-01-02T15:04:05Z"),
		}
		var data map[string]any
		if doc.Data != "" {
			json.Unmarshal([]byte(doc.Data), &data)
		}
		for k, v := range data {
			item[k] = v
		}
		items = append(items, item)
	}

	w.Header().Set("Content-Type", "application/json")
	w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%q", filename))

	encoder := json.NewEncoder(w)
	encoder.SetIndent("", "  ")
	encoder.Encode(items)
}

func (c *CollectionsController) exportCSV(w http.ResponseWriter, collection *models.Collection, docs []*models.Document, filename string) {
	// Get field names from schema
	fields, _ := content.GetFields(collection)

	// Build header
	headers := []string{"id", "created", "updated"}
	for _, f := range fields {
		headers = append(headers, f.Name)
	}

	w.Header().Set("Content-Type", "text/csv; charset=utf-8")
	w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%q", filename))

	// Write BOM for Excel
	w.Write([]byte{0xEF, 0xBB, 0xBF})

	// Write header
	w.Write([]byte(strings.Join(headers, ",") + "\n"))

	// Write rows
	for _, doc := range docs {
		var data map[string]any
		if doc.Data != "" {
			json.Unmarshal([]byte(doc.Data), &data)
		}

		row := []string{
			csvEscape(doc.ID),
			csvEscape(doc.CreatedAt.Format("2006-01-02T15:04:05Z")),
			csvEscape(doc.UpdatedAt.Format("2006-01-02T15:04:05Z")),
		}

		for _, f := range fields {
			val := data[f.Name]
			if val == nil {
				row = append(row, "")
			} else {
				switch v := val.(type) {
				case string:
					row = append(row, csvEscape(v))
				case []any, map[string]any:
					// JSON encode complex values
					bytes, _ := json.Marshal(v)
					row = append(row, csvEscape(string(bytes)))
				default:
					row = append(row, csvEscape(fmt.Sprintf("%v", v)))
				}
			}
		}

		w.Write([]byte(strings.Join(row, ",") + "\n"))
	}
}

func csvEscape(s string) string {
	needsQuote := strings.ContainsAny(s, ",\"\n\r")
	if needsQuote {
		s = strings.ReplaceAll(s, "\"", "\"\"")
		return "\"" + s + "\""
	}
	return s
}

// Import imports documents from JSON or CSV file.
// POST /admin/collections/{id}/import
// Form fields:
//   - file: the file to import
//   - format: json|csv (auto-detected from filename if not specified)
//   - mode: insert|upsert|replace (default: insert)
//   - skipInvalid: skip invalid rows instead of failing (default: false)
//   - stopOnError: stop at first error instead of collecting all (default: true)
func (c *CollectionsController) Import(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")
	mode := r.FormValue("mode")
	if mode == "" {
		mode = "insert"
	}
	skipInvalid := r.FormValue("skipInvalid") == "true"
	stopOnError := r.FormValue("stopOnError") != "false" // Default true

	// Check authorization
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermWrite) {
		c.RenderError(w, r, fmt.Errorf("Access denied"))
		return
	}

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("Collection not found"))
		return
	}

	// Get uploaded file
	r.ParseMultipartForm(MaxImportFileSize)
	file, header, err := r.FormFile("file")
	if err != nil {
		c.RenderError(w, r, fmt.Errorf("No import file provided"))
		return
	}
	defer file.Close()

	// Check file size
	if header.Size > MaxImportFileSize {
		c.RenderError(w, r, fmt.Errorf("File too large (max %dMB)", MaxImportFileSize>>20))
		return
	}

	// Detect format
	format := r.FormValue("format")
	if format == "" {
		if strings.HasSuffix(strings.ToLower(header.Filename), ".csv") {
			format = "csv"
		} else {
			format = "json"
		}
	}

	// Import based on format
	var inserted, updated, skipped int
	var errors []string

	switch format {
	case "json":
		inserted, updated, skipped, errors = c.importJSON(collection, file, mode, skipInvalid, stopOnError)
	case "csv":
		inserted, updated, skipped, errors = c.importCSV(collection, file, mode, skipInvalid, stopOnError)
	default:
		c.RenderError(w, r, fmt.Errorf("Unsupported format: %s", format))
		return
	}

	// Audit log
	helpers.AuditUpdate(r, user.ID, helpers.ResourceCollection, collectionID,
		fmt.Sprintf("%s import (%s): %d inserted, %d updated, %d skipped",
			collection.Name, format, inserted, updated, skipped), nil)

	if len(errors) > 0 && !skipInvalid {
		c.RenderError(w, r, fmt.Errorf("Import failed: %s", errors[0]))
		return
	}

	msg := fmt.Sprintf("Import complete: %d inserted, %d updated, %d skipped", inserted, updated, skipped)
	if len(errors) > 0 {
		msg += fmt.Sprintf(" (%d errors)", len(errors))
	}
	w.Header().Set("HX-Trigger", fmt.Sprintf(`{"showToast":%q}`, msg))
	c.Refresh(w, r)
}

func (c *CollectionsController) importJSON(collection *models.Collection, file io.Reader, mode string, skipInvalid, stopOnError bool) (inserted, updated, skipped int, errors []string) {
	// Decode JSON array
	var items []map[string]any
	if err := json.NewDecoder(file).Decode(&items); err != nil {
		return 0, 0, 0, []string{fmt.Sprintf("Invalid JSON: %v", err)}
	}

	// Check row limit
	if len(items) > MaxImportRows {
		return 0, 0, 0, []string{fmt.Sprintf("Too many rows: %d (max %d)", len(items), MaxImportRows)}
	}

	// If replace mode, delete existing first
	if mode == "replace" {
		existing, _ := models.Documents.Search("WHERE CollectionID = ?", collection.ID)
		for _, doc := range existing {
			models.Documents.Delete(doc)
		}
	}

	for i, item := range items {
		// Extract system fields if present
		id, _ := item["id"].(string)
		delete(item, "id")
		delete(item, "created")
		delete(item, "updated")
		delete(item, "collectionId")
		delete(item, "collectionName")

		// Validate against schema
		if err := content.ValidateDocument(collection, item); err != nil {
			if skipInvalid {
				skipped++
				continue
			}
			errors = append(errors, fmt.Sprintf("Row %d: %v", i+1, err))
			if stopOnError {
				return // Stop at first error
			}
			continue // Collect all errors
		}

		dataBytes, _ := json.Marshal(item)

		if mode == "upsert" && id != "" {
			// Try to find existing
			existing, _ := models.Documents.Get(id)
			if existing != nil && existing.CollectionID == collection.ID {
				existing.Data = string(dataBytes)
				models.Documents.Update(existing)
				updated++
				continue
			}
		}

		// Insert new
		doc := &models.Document{
			CollectionID: collection.ID,
			Data:         string(dataBytes),
		}
		models.Documents.Insert(doc)
		inserted++
	}

	return
}

func (c *CollectionsController) importCSV(collection *models.Collection, file interface{ Read([]byte) (int, error) }, mode string, skipInvalid, stopOnError bool) (inserted, updated, skipped int, errors []string) {
	// Read all data from file
	csvData := make([]byte, 0)
	buf := make([]byte, 4096)
	for {
		n, err := file.Read(buf)
		if n > 0 {
			csvData = append(csvData, buf[:n]...)
		}
		if err != nil {
			break
		}
	}

	// Remove BOM if present
	if len(csvData) >= 3 && csvData[0] == 0xEF && csvData[1] == 0xBB && csvData[2] == 0xBF {
		csvData = csvData[3:]
	}

	// Split into lines
	lines := strings.Split(string(csvData), "\n")
	if len(lines) < 2 {
		return 0, 0, 0, []string{"CSV must have header row and at least one data row"}
	}

	// Parse header
	headers := parseCSVLine(lines[0])
	if len(headers) == 0 {
		return 0, 0, 0, []string{"Invalid CSV header"}
	}

	// Count data rows (excluding empty lines)
	dataRowCount := 0
	for i := 1; i < len(lines); i++ {
		if strings.TrimSpace(lines[i]) != "" {
			dataRowCount++
		}
	}

	// Check row limit
	if dataRowCount > MaxImportRows {
		return 0, 0, 0, []string{fmt.Sprintf("Too many rows: %d (max %d)", dataRowCount, MaxImportRows)}
	}

	// If replace mode, delete existing first
	if mode == "replace" {
		existing, _ := models.Documents.Search("WHERE CollectionID = ?", collection.ID)
		for _, doc := range existing {
			models.Documents.Delete(doc)
		}
	}

	// Process data rows
	rowNum := 0
	for i := 1; i < len(lines); i++ {
		line := strings.TrimSpace(lines[i])
		if line == "" {
			continue
		}
		rowNum++

		values := parseCSVLine(line)
		if len(values) != len(headers) {
			if skipInvalid {
				skipped++
				continue
			}
			errors = append(errors, fmt.Sprintf("Row %d: column count mismatch", rowNum))
			if stopOnError {
				return
			}
			continue
		}

		// Build data map
		data := make(map[string]any)
		var id string
		for j, header := range headers {
			header = strings.TrimSpace(header)
			value := values[j]

			switch header {
			case "id":
				id = value
			case "created", "updated", "collectionId", "collectionName":
				// Skip system fields
			default:
				data[header] = parseCSVValue(value)
			}
		}

		// Validate against schema
		if err := content.ValidateDocument(collection, data); err != nil {
			if skipInvalid {
				skipped++
				continue
			}
			errors = append(errors, fmt.Sprintf("Row %d: %v", rowNum, err))
			if stopOnError {
				return
			}
			continue
		}

		dataBytes, _ := json.Marshal(data)

		if mode == "upsert" && id != "" {
			existing, _ := models.Documents.Get(id)
			if existing != nil && existing.CollectionID == collection.ID {
				existing.Data = string(dataBytes)
				models.Documents.Update(existing)
				updated++
				continue
			}
		}

		doc := &models.Document{
			CollectionID: collection.ID,
			Data:         string(dataBytes),
		}
		models.Documents.Insert(doc)
		inserted++
	}

	return
}

// parseCSVLine parses a CSV line, handling quoted fields.
func parseCSVLine(line string) []string {
	var result []string
	var current strings.Builder
	inQuotes := false

	for i := 0; i < len(line); i++ {
		ch := line[i]

		if ch == '"' {
			if inQuotes && i+1 < len(line) && line[i+1] == '"' {
				// Escaped quote
				current.WriteByte('"')
				i++
			} else {
				// Toggle quote mode
				inQuotes = !inQuotes
			}
		} else if ch == ',' && !inQuotes {
			// Field separator
			result = append(result, current.String())
			current.Reset()
		} else if ch == '\r' {
			// Ignore carriage return
		} else {
			current.WriteByte(ch)
		}
	}

	result = append(result, current.String())
	return result
}

// Import limits
const (
	MaxImportRows     = 10000
	MaxImportFileSize = 50 << 20 // 50MB
)

// ImportPreviewResponse is the response format for import preview.
type ImportPreviewResponse struct {
	TotalRows   int              `json:"totalRows"`
	ValidRows   int              `json:"validRows"`
	InvalidRows int              `json:"invalidRows"`
	Sample      []map[string]any `json:"sample"`
	Errors      []string         `json:"errors"`
	Headers     []string         `json:"headers,omitempty"` // For CSV only
}

// ImportPreview parses an import file and returns a preview of the data.
// POST /admin/collections/{id}/import/preview
func (c *CollectionsController) ImportPreview(w http.ResponseWriter, r *http.Request) {
	collectionID := r.PathValue("id")

	// Check authorization
	user := access.GetUserFromJWT(r)
	if !access.CheckAccess(user, access.ResourceCollection, collectionID, access.PermWrite) {
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusForbidden)
		json.NewEncoder(w).Encode(map[string]string{"error": "Access denied"})
		return
	}

	collection, err := models.Collections.Get(collectionID)
	if err != nil {
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusNotFound)
		json.NewEncoder(w).Encode(map[string]string{"error": "Collection not found"})
		return
	}

	// Get uploaded file
	r.ParseMultipartForm(MaxImportFileSize)
	file, header, err := r.FormFile("file")
	if err != nil {
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusBadRequest)
		json.NewEncoder(w).Encode(map[string]string{"error": "No import file provided"})
		return
	}
	defer file.Close()

	// Check file size
	if header.Size > MaxImportFileSize {
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusBadRequest)
		json.NewEncoder(w).Encode(map[string]string{"error": fmt.Sprintf("File too large (max %dMB)", MaxImportFileSize>>20)})
		return
	}

	// Detect format
	format := r.FormValue("format")
	if format == "" {
		if strings.HasSuffix(strings.ToLower(header.Filename), ".csv") {
			format = "csv"
		} else {
			format = "json"
		}
	}

	// Generate preview based on format
	var response ImportPreviewResponse
	switch format {
	case "json":
		response = c.previewJSON(collection, file)
	case "csv":
		response = c.previewCSV(collection, file)
	default:
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(http.StatusBadRequest)
		json.NewEncoder(w).Encode(map[string]string{"error": "Unsupported format: " + format})
		return
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(response)
}

func (c *CollectionsController) previewJSON(collection *models.Collection, file io.Reader) ImportPreviewResponse {
	var items []map[string]any
	if err := json.NewDecoder(file).Decode(&items); err != nil {
		return ImportPreviewResponse{
			Errors: []string{fmt.Sprintf("Invalid JSON: %v", err)},
		}
	}

	// Check row limit
	if len(items) > MaxImportRows {
		return ImportPreviewResponse{
			TotalRows: len(items),
			Errors:    []string{fmt.Sprintf("Too many rows: %d (max %d)", len(items), MaxImportRows)},
		}
	}

	response := ImportPreviewResponse{
		TotalRows: len(items),
		Sample:    make([]map[string]any, 0, 10),
		Errors:    make([]string, 0),
	}

	for i, item := range items {
		// Remove system fields for validation
		itemCopy := make(map[string]any)
		for k, v := range item {
			if k != "id" && k != "created" && k != "updated" && k != "collectionId" && k != "collectionName" {
				itemCopy[k] = v
			}
		}

		// Validate against schema
		if err := content.ValidateDocument(collection, itemCopy); err != nil {
			response.InvalidRows++
			if len(response.Errors) < 10 { // Limit errors shown
				response.Errors = append(response.Errors, fmt.Sprintf("Row %d: %v", i+1, err))
			}
		} else {
			response.ValidRows++
		}

		// Add to sample (first 10 rows)
		if len(response.Sample) < 10 {
			response.Sample = append(response.Sample, item)
		}
	}

	return response
}

func (c *CollectionsController) previewCSV(collection *models.Collection, file interface{ Read([]byte) (int, error) }) ImportPreviewResponse {
	// Read all data from file
	csvData := make([]byte, 0)
	buf := make([]byte, 4096)
	for {
		n, err := file.Read(buf)
		if n > 0 {
			csvData = append(csvData, buf[:n]...)
		}
		if err != nil {
			break
		}
	}

	// Remove BOM if present
	if len(csvData) >= 3 && csvData[0] == 0xEF && csvData[1] == 0xBB && csvData[2] == 0xBF {
		csvData = csvData[3:]
	}

	// Split into lines
	lines := strings.Split(string(csvData), "\n")
	if len(lines) < 2 {
		return ImportPreviewResponse{
			Errors: []string{"CSV must have header row and at least one data row"},
		}
	}

	// Parse header
	headers := parseCSVLine(lines[0])
	if len(headers) == 0 {
		return ImportPreviewResponse{
			Errors: []string{"Invalid CSV header"},
		}
	}

	// Count data rows (excluding empty lines)
	dataRowCount := 0
	for i := 1; i < len(lines); i++ {
		if strings.TrimSpace(lines[i]) != "" {
			dataRowCount++
		}
	}

	// Check row limit
	if dataRowCount > MaxImportRows {
		return ImportPreviewResponse{
			TotalRows: dataRowCount,
			Headers:   headers,
			Errors:    []string{fmt.Sprintf("Too many rows: %d (max %d)", dataRowCount, MaxImportRows)},
		}
	}

	response := ImportPreviewResponse{
		TotalRows: dataRowCount,
		Headers:   headers,
		Sample:    make([]map[string]any, 0, 10),
		Errors:    make([]string, 0),
	}

	rowNum := 0
	for i := 1; i < len(lines); i++ {
		line := strings.TrimSpace(lines[i])
		if line == "" {
			continue
		}
		rowNum++

		values := parseCSVLine(line)
		if len(values) != len(headers) {
			response.InvalidRows++
			if len(response.Errors) < 10 {
				response.Errors = append(response.Errors, fmt.Sprintf("Row %d: column count mismatch (expected %d, got %d)", rowNum, len(headers), len(values)))
			}
			continue
		}

		// Build data map
		data := make(map[string]any)
		for j, header := range headers {
			header = strings.TrimSpace(header)
			value := values[j]

			// Skip system fields
			if header == "id" || header == "created" || header == "updated" || header == "collectionId" || header == "collectionName" {
				continue
			}

			// Parse value according to type
			data[header] = parseCSVValue(value)
		}

		// Validate against schema
		if err := content.ValidateDocument(collection, data); err != nil {
			response.InvalidRows++
			if len(response.Errors) < 10 {
				response.Errors = append(response.Errors, fmt.Sprintf("Row %d: %v", rowNum, err))
			}
		} else {
			response.ValidRows++
		}

		// Add to sample (first 10 rows)
		if len(response.Sample) < 10 {
			// Include all fields in sample
			sample := make(map[string]any)
			for j, header := range headers {
				sample[strings.TrimSpace(header)] = parseCSVValue(values[j])
			}
			response.Sample = append(response.Sample, sample)
		}
	}

	return response
}

// parseCSVValue parses a CSV cell value to the appropriate type.
func parseCSVValue(value string) any {
	// Try to parse as JSON for complex types
	if strings.HasPrefix(value, "[") || strings.HasPrefix(value, "{") {
		var parsed any
		if err := json.Unmarshal([]byte(value), &parsed); err == nil {
			return parsed
		}
	}

	// Try to parse as number
	if value != "" && (value[0] >= '0' && value[0] <= '9' || value[0] == '-') {
		var f float64
		if _, err := fmt.Sscanf(value, "%f", &f); err == nil {
			return f
		}
	}

	// Try to parse as boolean
	if value == "true" {
		return true
	}
	if value == "false" {
		return false
	}

	// Return as string
	return value
}