package tools

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

	"skraak/db"
	"skraak/utils"
)

// ImportFileInput defines the input parameters for the import_file tool
type ImportFileInput struct {
	FilePath   string `json:"file_path"`
	DatasetID  string `json:"dataset_id"`
	LocationID string `json:"location_id"`
	ClusterID  string `json:"cluster_id"`
}

// ImportFileOutput defines the output structure for the import_file tool
type ImportFileOutput struct {
	FileID         string    `json:"file_id"`
	FileName       string    `json:"file_name"`
	Hash           string    `json:"hash"`
	Duration       float64   `json:"duration_seconds"`
	SampleRate     int       `json:"sample_rate"`
	TimestampLocal time.Time `json:"timestamp_local"`
	IsAudioMoth    bool      `json:"is_audiomoth"`
	IsDuplicate    bool      `json:"is_duplicate"`
	ProcessingTime string    `json:"processing_time"`
	Error          *string   `json:"error,omitempty"`
}

// ImportFile imports a single WAV file into the database with duplicate detection
func ImportFile(
	ctx context.Context,
	input ImportFileInput,
) (ImportFileOutput, error) {
	startTime := time.Now()
	var output ImportFileOutput

	// Phase 1: Validate file path
	_, err := validateFilePath(input.FilePath)
	if err != nil {
		return output, fmt.Errorf("file validation failed: %w", err)
	}
	output.FileName = filepath.Base(input.FilePath)

	// Phase 2: Validate database hierarchy
	if err := validateHierarchyIDs(input.DatasetID, input.LocationID, input.ClusterID, dbPath); err != nil {
		return output, fmt.Errorf("hierarchy validation failed: %w", err)
	}

	// Phase 3: Open database connection (single connection for all DB operations)
	database, err := db.OpenWriteableDB(dbPath)
	if err != nil {
		return output, fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()

	// Phase 4: Get location data for astronomical calculations
	locData, err := utils.GetLocationData(database, input.LocationID)
	if err != nil {
		return output, fmt.Errorf("failed to get location data: %w", err)
	}

	// Phase 5: Process file metadata
	result, err := utils.ProcessSingleFile(input.FilePath, locData.Latitude, locData.Longitude, locData.TimezoneID, true)
	if err != nil {
		errMsg := err.Error()
		output.Error = &errMsg
		output.ProcessingTime = time.Since(startTime).String()
		return output, fmt.Errorf("file processing failed: %w", err)
	}

	// Populate output with extracted metadata
	output.FileName = result.FileName
	output.Hash = result.Hash
	output.Duration = result.Duration
	output.SampleRate = result.SampleRate
	output.TimestampLocal = result.TimestampLocal
	output.IsAudioMoth = result.IsAudioMoth

	// Phase 6: Ensure cluster path is set
	if err := utils.EnsureClusterPath(database, input.ClusterID, filepath.Dir(input.FilePath)); err != nil {
		return output, fmt.Errorf("failed to set cluster path: %w", err)
	}

	// Phase 7: Insert into database
	fileID, isDuplicate, err := insertFileIntoDB(ctx, database, result, input.DatasetID, input.ClusterID, input.LocationID)
	if err != nil {
		errMsg := err.Error()
		output.Error = &errMsg
		output.ProcessingTime = time.Since(startTime).String()
		return output, fmt.Errorf("database insertion failed: %w", err)
	}

	output.FileID = fileID
	output.IsDuplicate = isDuplicate
	output.ProcessingTime = time.Since(startTime).String()

	return output, nil
}

// validateFilePath validates the file exists, is a regular file, is a WAV file, and is not empty
func validateFilePath(filePath string) (os.FileInfo, error) {
	// Check file exists
	info, err := os.Stat(filePath)
	if err != nil {
		if os.IsNotExist(err) {
			return nil, fmt.Errorf("file does not exist: %s", filePath)
		}
		return nil, fmt.Errorf("cannot access file: %w", err)
	}

	// Check it's a regular file
	if !info.Mode().IsRegular() {
		return nil, fmt.Errorf("path is not a regular file: %s", filePath)
	}

	// Check extension is .wav (case-insensitive)
	ext := strings.ToLower(filepath.Ext(filePath))
	if ext != ".wav" {
		return nil, fmt.Errorf("file must be a WAV file (got extension: %s)", ext)
	}

	// Check file is not empty
	if info.Size() == 0 {
		return nil, fmt.Errorf("file is empty: %s", filePath)
	}

	return info, nil
}

// insertFileIntoDB inserts a single file into the database
// Returns (fileID, isDuplicate, error)
func insertFileIntoDB(
	ctx context.Context,
	database *sql.DB,
	result *utils.FileProcessingResult,
	datasetID, clusterID, locationID string,
) (string, bool, error) {
	// Begin logged transaction
	tx, err := db.BeginLoggedTx(ctx, database, "import_audio_file")
	if err != nil {
		return "", false, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() // Rollback if not committed

	// Check for duplicate hash
	existingID, isDup, err := utils.CheckDuplicateHash(tx, result.Hash)
	if err != nil {
		return "", false, err
	}
	if isDup {
		return existingID, true, nil
	}

	// Generate file ID
	fileID, err := utils.GenerateLongID()
	if err != nil {
		return "", false, fmt.Errorf("ID generation failed: %w", err)
	}

	// Insert file record
	_, err = tx.ExecContext(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)
	`,
		fileID, result.FileName, result.Hash, locationID,
		result.TimestampLocal, clusterID, result.Duration, result.SampleRate,
		result.AstroData.SolarNight, result.AstroData.CivilNight, result.AstroData.MoonPhase,
	)
	if err != nil {
		return "", false, fmt.Errorf("file insert failed: %w", err)
	}

	// Insert file_dataset junction
	_, err = tx.ExecContext(ctx, `
		INSERT INTO file_dataset (file_id, dataset_id, created_at, last_modified)
		VALUES (?, ?, now(), now())
	`, fileID, datasetID)
	if err != nil {
		return "", false, fmt.Errorf("file_dataset insert failed: %w", err)
	}

	// If AudioMoth, insert moth_metadata
	if result.IsAudioMoth && result.MothData != nil {
		_, err = tx.ExecContext(ctx, `
			INSERT INTO moth_metadata (
				file_id, timestamp, recorder_id, gain, battery_v, temp_c,
				created_at, last_modified, active
			) VALUES (?, ?, ?, ?, ?, ?, now(), now(), true)
		`,
			fileID,
			result.MothData.Timestamp,
			&result.MothData.RecorderID,
			&result.MothData.Gain,
			&result.MothData.BatteryV,
			&result.MothData.TempC,
		)
		if err != nil {
			return "", false, fmt.Errorf("moth_metadata insert failed: %w", err)
		}
	}

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

	return fileID, false, nil
}