package utils

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"time"
)

// FileImportError records errors encountered during file processing
type FileImportError struct {
	FileName string `json:"file_name"`
	Error    string `json:"error"`
	Stage    string `json:"stage"` // "scan", "hash", "parse", "validate", "insert"
}

// ClusterImportInput defines parameters for importing one cluster
type ClusterImportInput struct {
	FolderPath string // Absolute path to folder with WAV files
	DatasetID  string // 12-char dataset ID
	LocationID string // 12-char location ID
	ClusterID  string // 12-char cluster ID
	Recursive  bool   // Scan subfolders?
}

// ClusterImportOutput provides results and statistics
type ClusterImportOutput struct {
	TotalFiles     int
	ImportedFiles  int
	SkippedFiles   int // Duplicates
	FailedFiles    int
	AudioMothFiles int
	TotalDuration  float64
	ProcessingTime string
	Errors         []FileImportError
}

// locationData holds location information needed for processing
type locationData struct {
	Latitude   float64
	Longitude  float64
	TimezoneID string
}

// fileData holds all data for a single file to be imported
type fileData struct {
	FileName       string
	Hash           string
	Duration       float64
	SampleRate     int
	TimestampLocal time.Time
	IsAudioMoth    bool
	MothData       *AudioMothData
	AstroData      AstronomicalData
}

// ImportCluster imports all WAV files from a folder into a cluster
//
// This is the canonical cluster import logic used by both:
//   - import_files.go (single cluster)
//   - bulk_file_import.go (multiple clusters)
//
// Steps:
//  1. Validate folder exists
//  2. Get location metadata (lat/lon/timezone) from database
//  3. Scan folder for WAV files (recursive or not)
//  4. Batch process all files:
//     - Parse WAV headers (includes file mod time)
//     - Batch parse filename timestamps (variance-based)
//     - Resolve timestamps (AudioMoth → filename → file mod time)
//     - Calculate hashes
//     - Calculate astronomical data
//  5. Batch insert in single transaction:
//     - Check duplicates
//     - INSERT INTO file
//     - INSERT INTO file_dataset (ALWAYS)
//     - INSERT INTO moth_metadata (if AudioMoth)
//     - All-or-nothing commit
//  6. Return summary statistics
//
// Transaction behavior: ALL files succeed or ALL rollback
// This preserves cluster integrity (cluster = complete recording session)
func ImportCluster(
	database *sql.DB,
	input ClusterImportInput,
) (*ClusterImportOutput, error) {
	startTime := time.Now()

	// Validate folder exists
	info, err := os.Stat(input.FolderPath)
	if err != nil {
		return nil, fmt.Errorf("folder not accessible: %w", err)
	}
	if !info.IsDir() {
		return nil, fmt.Errorf("path is not a directory: %s", input.FolderPath)
	}

	// Get location data for astronomical calculations
	locationData, err := getLocationData(database, input.LocationID)
	if err != nil {
		return nil, fmt.Errorf("failed to get location data: %w", err)
	}

	// Scan folder for WAV files
	wavFiles, err := scanClusterFiles(input.FolderPath, input.Recursive)
	if err != nil {
		return nil, fmt.Errorf("failed to scan folder: %w", err)
	}

	// If no files, return early
	if len(wavFiles) == 0 {
		return &ClusterImportOutput{
			TotalFiles:     0,
			ProcessingTime: time.Since(startTime).String(),
			Errors:         []FileImportError{},
		}, nil
	}

	// Batch process all files
	filesData, processErrors := batchProcessFiles(wavFiles, locationData)

	// Batch insert into database
	imported, skipped, insertErrors, err := insertClusterFiles(
		database,
		filesData,
		input.DatasetID,
		input.ClusterID,
		input.LocationID,
	)
	if err != nil {
		return nil, fmt.Errorf("database insertion failed: %w", err)
	}

	// Combine all errors
	allErrors := append(processErrors, insertErrors...)

	// Calculate summary statistics
	audiomothCount := 0
	totalDuration := 0.0
	for _, fd := range filesData {
		if fd.IsAudioMoth {
			audiomothCount++
		}
		totalDuration += fd.Duration
	}

	return &ClusterImportOutput{
		TotalFiles:     len(wavFiles),
		ImportedFiles:  imported,
		SkippedFiles:   skipped,
		FailedFiles:    len(allErrors),
		AudioMothFiles: audiomothCount,
		TotalDuration:  totalDuration,
		ProcessingTime: time.Since(startTime).String(),
		Errors:         allErrors,
	}, nil
}

// getLocationData retrieves location coordinates and timezone
func getLocationData(database *sql.DB, locationID string) (*locationData, error) {
	var loc locationData
	err := database.QueryRow(
		"SELECT latitude, longitude, timezone_id FROM location WHERE id = ?",
		locationID,
	).Scan(&loc.Latitude, &loc.Longitude, &loc.TimezoneID)

	if err != nil {
		return nil, fmt.Errorf("failed to query location data: %w", err)
	}

	return &loc, nil
}

// EnsureClusterPath sets the cluster's path field if it's currently empty
func EnsureClusterPath(database *sql.DB, clusterID, folderPath string) error {
	// Check if cluster already has a path
	var currentPath sql.NullString
	err := database.QueryRow("SELECT path FROM cluster WHERE id = ?", clusterID).Scan(&currentPath)
	if err != nil {
		return fmt.Errorf("failed to query cluster: %w", err)
	}

	// If path is already set, skip
	if currentPath.Valid && currentPath.String != "" {
		return nil
	}

	// Normalize folder path
	normalizedPath := NormalizeFolderPath(folderPath)

	// Update cluster with normalized path
	_, err = database.Exec(
		"UPDATE cluster SET path = ?, last_modified = now() WHERE id = ?",
		normalizedPath,
		clusterID,
	)
	if err != nil {
		return fmt.Errorf("failed to update cluster path: %w", err)
	}

	return nil
}

// scanClusterFiles recursively scans a folder for WAV files, excluding Clips_* subfolders
func scanClusterFiles(rootPath string, recursive bool) ([]string, error) {
	var wavFiles []string

	if recursive {
		err := filepath.Walk(rootPath, func(path string, info os.FileInfo, err error) error {
			if err != nil {
				return err
			}

			// Skip "Clips_*" directories
			if info.IsDir() && strings.HasPrefix(info.Name(), "Clips_") {
				return filepath.SkipDir
			}

			// Check for WAV files
			if !info.IsDir() {
				ext := strings.ToLower(filepath.Ext(path))
				if ext == ".wav" && info.Size() > 0 {
					wavFiles = append(wavFiles, path)
				}
			}

			return nil
		})

		if err != nil {
			return nil, err
		}
	} else {
		// Non-recursive: scan only top level
		entries, err := os.ReadDir(rootPath)
		if err != nil {
			return nil, err
		}

		for _, entry := range entries {
			if !entry.IsDir() {
				name := entry.Name()
				ext := strings.ToLower(filepath.Ext(name))
				if ext == ".wav" {
					path := filepath.Join(rootPath, name)
					if info, err := os.Stat(path); err == nil && info.Size() > 0 {
						wavFiles = append(wavFiles, path)
					}
				}
			}
		}
	}

	// Sort for consistent processing order
	sort.Strings(wavFiles)

	return wavFiles, nil
}

// batchProcessFiles extracts metadata and calculates hashes for all files
func batchProcessFiles(wavFiles []string, location *locationData) ([]*fileData, []FileImportError) {
	var filesData []*fileData
	var errors []FileImportError

	// Step 1: Extract WAV metadata from all files
	type wavInfo struct {
		path     string
		metadata *WAVMetadata
		err      error
	}

	wavInfos := make([]wavInfo, len(wavFiles))
	for i, path := range wavFiles {
		metadata, err := ParseWAVHeader(path)
		wavInfos[i] = wavInfo{path: path, metadata: metadata, err: err}
	}

	// Step 2: Collect filenames for batch timestamp parsing
	var filenamesForParsing []string
	var filenameIndices []int

	for i, info := range wavInfos {
		if info.err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    info.err.Error(),
				Stage:    "parse",
			})
			continue
		}

		// Check if file has timestamp filename format
		if HasTimestampFilename(info.path) {
			filenamesForParsing = append(filenamesForParsing, filepath.Base(info.path))
			filenameIndices = append(filenameIndices, i)
		}
	}

	// Step 3: Parse filename timestamps in batch (if any)
	filenameTimestampMap := make(map[int]time.Time) // Maps file index to timestamp

	if len(filenamesForParsing) > 0 {
		filenameTimestamps, err := ParseFilenameTimestamps(filenamesForParsing)
		if err != nil {
			// If batch parsing fails, record error for all files
			for _, idx := range filenameIndices {
				errors = append(errors, FileImportError{
					FileName: filepath.Base(wavInfos[idx].path),
					Error:    fmt.Sprintf("filename timestamp parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		} else {
			// Apply timezone offset
			adjustedTimestamps, err := ApplyTimezoneOffset(filenameTimestamps, location.TimezoneID)
			if err != nil {
				for _, idx := range filenameIndices {
					errors = append(errors, FileImportError{
						FileName: filepath.Base(wavInfos[idx].path),
						Error:    fmt.Sprintf("timezone offset failed: %v", err),
						Stage:    "parse",
					})
				}
			} else {
				// Build map from file index to timestamp
				for j, idx := range filenameIndices {
					filenameTimestampMap[idx] = adjustedTimestamps[j]
				}
			}
		}
	}

	// Step 4: Process each file
	for i, info := range wavInfos {
		if info.err != nil {
			continue // Already recorded error
		}

		// Calculate hash
		hash, err := ComputeXXH64(info.path)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    fmt.Sprintf("hash calculation failed: %v", err),
				Stage:    "hash",
			})
			continue
		}

		// Determine timestamp
		var timestampLocal time.Time
		var isAudioMoth bool
		var mothData *AudioMothData

		// Try AudioMoth comment first
		if IsAudioMoth(info.metadata.Comment, info.metadata.Artist) {
			isAudioMoth = true
			mothData, err = ParseAudioMothComment(info.metadata.Comment)
			if err == nil {
				timestampLocal = mothData.Timestamp
			} else {
				// AudioMoth detected but parsing failed - try filename
				errors = append(errors, FileImportError{
					FileName: filepath.Base(info.path),
					Error:    fmt.Sprintf("AudioMoth comment parsing failed: %v", err),
					Stage:    "parse",
				})
			}
		}

		// If no AudioMoth timestamp, use filename timestamp
		if timestampLocal.IsZero() {
			if ts, ok := filenameTimestampMap[i]; ok {
				timestampLocal = ts
			}
		}

		// If still no timestamp, use file modification time as fallback
		if timestampLocal.IsZero() {
			if !info.metadata.FileModTime.IsZero() {
				// Assume FileModTime is already in location timezone
				// (recorder was at the location when it recorded)
				timestampLocal = info.metadata.FileModTime
			}
		}

		// If still no timestamp, skip file
		if timestampLocal.IsZero() {
			errors = append(errors, FileImportError{
				FileName: filepath.Base(info.path),
				Error:    "no timestamp available (not AudioMoth, filename not parseable, and file mod time missing)",
				Stage:    "parse",
			})
			continue
		}

		// Calculate astronomical data
		astroData := CalculateAstronomicalData(
			timestampLocal.UTC(),
			info.metadata.Duration,
			location.Latitude,
			location.Longitude,
		)

		// Add to results
		filesData = append(filesData, &fileData{
			FileName:       filepath.Base(info.path),
			Hash:           hash,
			Duration:       info.metadata.Duration,
			SampleRate:     info.metadata.SampleRate,
			TimestampLocal: timestampLocal,
			IsAudioMoth:    isAudioMoth,
			MothData:       mothData,
			AstroData:      astroData,
		})
	}

	return filesData, errors
}

// insertClusterFiles inserts all file data into database in a single transaction
func insertClusterFiles(
	database *sql.DB,
	filesData []*fileData,
	datasetID, clusterID, locationID string,
) (imported, skipped int, errors []FileImportError, err error) {
	// Begin transaction
	ctx := context.Background()
	tx, err := database.BeginTx(ctx, nil)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() // Rollback if not committed

	// Prepare statements
	fileStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file (
			id, file_name, xxh64_hash, location_id, timestamp_local,
			cluster_id, duration, sample_rate, maybe_solar_night, maybe_civil_night,
			moon_phase, created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare file statement: %w", err)
	}
	defer fileStmt.Close()

	datasetStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO file_dataset (file_id, dataset_id, created_at, last_modified)
		VALUES (?, ?, now(), now())
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare dataset statement: %w", err)
	}
	defer datasetStmt.Close()

	mothStmt, err := tx.PrepareContext(ctx, `
		INSERT INTO moth_metadata (
			file_id, timestamp, recorder_id, gain, battery_v, temp_c,
			created_at, last_modified, active
		) VALUES (?, ?, ?, ?, ?, ?, now(), now(), true)
	`)
	if err != nil {
		return 0, 0, nil, fmt.Errorf("failed to prepare moth statement: %w", err)
	}
	defer mothStmt.Close()

	// Insert each file
	for _, fd := range filesData {
		// Check for duplicate hash
		var exists bool
		err = tx.QueryRowContext(ctx,
			"SELECT EXISTS(SELECT 1 FROM file WHERE xxh64_hash = ?)",
			fd.Hash,
		).Scan(&exists)

		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("duplicate check failed: %v", err),
				Stage:    "insert",
			})
			continue
		}

		if exists {
			skipped++
			continue
		}

		// Generate file ID
		fileID, err := GenerateLongID()
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("ID generation failed: %v", err),
				Stage:    "insert",
			})
			continue
		}

		// Insert file record
		_, err = fileStmt.ExecContext(ctx,
			fileID, fd.FileName, fd.Hash, locationID,
			fd.TimestampLocal, clusterID, fd.Duration, fd.SampleRate,
			fd.AstroData.SolarNight, fd.AstroData.CivilNight, fd.AstroData.MoonPhase,
		)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}

		// Insert file_dataset junction (ALWAYS)
		_, err = datasetStmt.ExecContext(ctx, fileID, datasetID)
		if err != nil {
			errors = append(errors, FileImportError{
				FileName: fd.FileName,
				Error:    fmt.Sprintf("file_dataset insert failed: %v", err),
				Stage:    "insert",
			})
			continue
		}

		// If AudioMoth, insert moth_metadata
		if fd.IsAudioMoth && fd.MothData != nil {
			_, err = mothStmt.ExecContext(ctx,
				fileID,
				fd.MothData.Timestamp,
				&fd.MothData.RecorderID,
				&fd.MothData.Gain,
				&fd.MothData.BatteryV,
				&fd.MothData.TempC,
			)
			if err != nil {
				errors = append(errors, FileImportError{
					FileName: fd.FileName,
					Error:    fmt.Sprintf("moth_metadata insert failed: %v", err),
					Stage:    "insert",
				})
				continue
			}
		}

		imported++
	}

	// Commit transaction
	err = tx.Commit()
	if err != nil {
		return 0, 0, errors, fmt.Errorf("transaction commit failed: %w", err)
	}

	return imported, skipped, errors, nil
}