package sqldir_test

import (
	"fmt"
	"log"

	"git.warky.dev/wdevs/relspecgo/pkg/readers"
	"git.warky.dev/wdevs/relspecgo/pkg/readers/sqldir"
	"git.warky.dev/wdevs/relspecgo/pkg/writers"
	"git.warky.dev/wdevs/relspecgo/pkg/writers/sqlexec"
)

// Example demonstrates how to read SQL scripts from a directory and execute them
func Example() {
	// Step 1: Read SQL scripts from a directory
	// Directory structure example:
	//   migrations/
	//     1_001_create_schema.sql
	//     1_002_create_users_table.sql
	//     1_003_create_posts_table.pgsql
	//     2_001_add_indexes.sql
	//     2_002_seed_data.sql

	reader := sqldir.NewReader(&readers.ReaderOptions{
		FilePath: "/path/to/migrations",
		Metadata: map[string]any{
			"schema_name":   "public",
			"database_name": "myapp",
		},
	})

	// Read the database schema with scripts
	database, err := reader.ReadDatabase()
	if err != nil {
		log.Fatalf("Failed to read scripts: %v", err)
	}

	fmt.Printf("Read %d schemas\n", len(database.Schemas))
	fmt.Printf("Found %d scripts in schema '%s'\n",
		len(database.Schemas[0].Scripts),
		database.Schemas[0].Name)

	// Step 2: Execute the scripts against a PostgreSQL database
	writer := sqlexec.NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://user:password@localhost:5432/myapp?sslmode=disable",
		},
	})

	// Execute all scripts in Priority then Sequence order
	if err := writer.WriteDatabase(database); err != nil {
		log.Fatalf("Failed to execute scripts: %v", err)
	}

	fmt.Println("All scripts executed successfully!")
}

// Example_withSingleSchema shows how to read and execute scripts for a single schema
func Example_withSingleSchema() {
	// Read scripts
	reader := sqldir.NewReader(&readers.ReaderOptions{
		FilePath: "/path/to/migrations",
	})

	schema, err := reader.ReadSchema()
	if err != nil {
		log.Fatalf("Failed to read schema: %v", err)
	}

	// Execute scripts
	writer := sqlexec.NewWriter(&writers.WriterOptions{
		Metadata: map[string]any{
			"connection_string": "postgres://localhost/testdb",
		},
	})

	if err := writer.WriteSchema(schema); err != nil {
		log.Fatalf("Failed to execute scripts: %v", err)
	}

	fmt.Println("Schema scripts executed successfully!")
}

// Example_fileNamingConvention shows the expected file naming pattern
func Example_fileNamingConvention() {
	// File naming pattern: {priority}_{sequence}_{name}.sql or .pgsql
	//                  OR: {priority}-{sequence}-{name}.sql or .pgsql
	//
	// Both underscore (_) and hyphen (-) separators are supported and can be mixed.
	//
	// Components:
	//   - priority: Integer (0-9999) - Scripts with lower priority execute first
	//   - sequence: Integer (0-9999) - Within same priority, lower sequence executes first
	//   - separator: Underscore (_) or hyphen (-)
	//   - name: Descriptive name (alphanumeric, underscores, hyphens)
	//   - extension: .sql or .pgsql
	//
	// Examples (underscore format):
	//   ✓ 1_001_create_users.sql        (Priority=1, Sequence=1)
	//   ✓ 1_002_create_posts.sql        (Priority=1, Sequence=2)
	//   ✓ 2_001_add_indexes.pgsql       (Priority=2, Sequence=1)
	//   ✓ 10_100_migration.sql          (Priority=10, Sequence=100)
	//
	// Examples (hyphen format):
	//   ✓ 1-001-create-users.sql        (Priority=1, Sequence=1)
	//   ✓ 1-002-create-posts.sql        (Priority=1, Sequence=2)
	//   ✓ 2-001-add-indexes.pgsql       (Priority=2, Sequence=1)
	//   ✓ 10-10-create-newid.pgsql      (Priority=10, Sequence=10)
	//
	// Mixed format (both in same directory):
	//   ✓ 1_001_create_users.sql        (underscore format)
	//   ✓ 1-002-create-posts.sql        (hyphen format)
	//   ✓ 2_001_add_indexes.sql         (underscore format)
	//
	// Execution order for mixed examples:
	//   1. 1_001_create_users.sql       (Priority 1, Sequence 1)
	//   2. 1-002-create-posts.sql       (Priority 1, Sequence 2)
	//   3. 2_001_add_indexes.sql        (Priority 2, Sequence 1)
	//
	// Invalid filenames (will be ignored):
	//   ✗ migration.sql                 (missing priority/sequence)
	//   ✗ 1_create_users.sql           (missing sequence)
	//   ✗ create_users.sql             (missing priority/sequence)
	//   ✗ 1_001_create_users.txt       (wrong extension)

	fmt.Println("See comments for file naming conventions")
}