package sqldir

import (
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strconv"

	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/readers"
)

// Reader implements the readers.Reader interface for SQL script directories
type Reader struct {
	options *readers.ReaderOptions
}

// NewReader creates a new SQL directory reader
func NewReader(options *readers.ReaderOptions) *Reader {
	return &Reader{
		options: options,
	}
}

// ReadDatabase reads all SQL scripts from a directory into a Database
func (r *Reader) ReadDatabase() (*models.Database, error) {
	if r.options.FilePath == "" {
		return nil, fmt.Errorf("directory path is required")
	}

	// Check if directory exists
	info, err := os.Stat(r.options.FilePath)
	if err != nil {
		return nil, fmt.Errorf("failed to access directory: %w", err)
	}
	if !info.IsDir() {
		return nil, fmt.Errorf("path is not a directory: %s", r.options.FilePath)
	}

	// Read scripts from directory
	scripts, err := r.readScripts()
	if err != nil {
		return nil, fmt.Errorf("failed to read scripts: %w", err)
	}

	// Get schema name from metadata or use default
	schemaName := "public"
	if name, ok := r.options.Metadata["schema_name"].(string); ok && name != "" {
		schemaName = name
	}

	// Create schema with scripts
	schema := &models.Schema{
		Name:    schemaName,
		Scripts: scripts,
	}

	// Get database name from metadata or use default
	dbName := "database"
	if name, ok := r.options.Metadata["database_name"].(string); ok && name != "" {
		dbName = name
	}

	// Create database with schema
	database := &models.Database{
		Name:    dbName,
		Schemas: []*models.Schema{schema},
	}

	// Set back-reference
	schema.RefDatabase = database

	return database, nil
}

// ReadSchema reads all SQL scripts from a directory into a Schema
func (r *Reader) ReadSchema() (*models.Schema, error) {
	db, err := r.ReadDatabase()
	if err != nil {
		return nil, err
	}
	if len(db.Schemas) == 0 {
		return nil, fmt.Errorf("no schema found")
	}
	return db.Schemas[0], nil
}

// ReadTable is not applicable for SQL script directories
func (r *Reader) ReadTable() (*models.Table, error) {
	return nil, fmt.Errorf("ReadTable is not supported for SQL script directories")
}

// readScripts recursively scans the directory for SQL files and parses them into Script models
func (r *Reader) readScripts() ([]*models.Script, error) {
	var scripts []*models.Script

	// Regular expression to parse filename: {priority}{sep}{sequence}{sep}{name}.sql or .pgsql
	// Separator can be underscore (_) or hyphen (-)
	// Example: 1_001_create_users.sql -> priority=1, sequence=001, name=create_users
	// Example: 2_005_add_indexes.pgsql -> priority=2, sequence=005, name=add_indexes
	// Example: 10-10-create-newid.pgsql -> priority=10, sequence=10, name=create-newid
	pattern := regexp.MustCompile(`^(\d+)[_-](\d+)[_-](.+)\.(sql|pgsql)$`)

	err := filepath.WalkDir(r.options.FilePath, func(path string, d os.DirEntry, err error) error {
		if err != nil {
			return err
		}

		// Skip directories
		if d.IsDir() {
			return nil
		}

		// Get filename
		filename := d.Name()

		// Match against pattern
		matches := pattern.FindStringSubmatch(filename)
		if matches == nil {
			// Skip files that don't match the pattern
			return nil
		}

		// Parse priority
		priority, err := strconv.Atoi(matches[1])
		if err != nil {
			return fmt.Errorf("invalid priority in filename %s: %w", filename, err)
		}

		// Parse sequence
		sequence, err := strconv.ParseUint(matches[2], 10, 64)
		if err != nil {
			return fmt.Errorf("invalid sequence in filename %s: %w", filename, err)
		}

		// Extract name
		name := matches[3]

		// Read SQL content
		content, err := os.ReadFile(path)
		if err != nil {
			return fmt.Errorf("failed to read file %s: %w", path, err)
		}

		// Get relative path from base directory
		relPath, err := filepath.Rel(r.options.FilePath, path)
		if err != nil {
			relPath = path
		}

		// Create Script model
		script := models.InitScript(name)
		script.Description = fmt.Sprintf("SQL script from %s", relPath)
		script.SQL = string(content)
		script.Priority = priority
		script.Sequence = uint(sequence)

		scripts = append(scripts, script)

		return nil
	})

	if err != nil {
		return nil, err
	}

	return scripts, nil
}