# RelSpec Scripts Command

The `relspec scripts` command provides tools for managing and executing SQL migration scripts from a directory structure.

## Overview

The scripts command supports two main operations:
- **list**: List SQL scripts from a directory in execution order
- **execute**: Execute SQL scripts against a PostgreSQL database

Scripts are read from a directory (recursively) and executed in a deterministic order based on **Priority** (ascending) and **Sequence** (ascending).

## File Naming Convention

SQL scripts must follow this naming pattern (both separators are supported):

```
{priority}_{sequence}_{name}.{sql|pgsql}  (underscore format)
{priority}-{sequence}-{name}.{sql|pgsql}  (hyphen format)
```

### Components

- **priority**: Integer (0-9999) - Execution priority level (lower executes first)
- **sequence**: Integer (0-9999) - Order within priority level (lower executes first)
- **separator**: Underscore `_` or hyphen `-` (both formats can be mixed)
- **name**: Descriptive name (alphanumeric, underscores, hyphens)
- **extension**: `.sql` or `.pgsql`

### Valid Examples

**Underscore format:**
```
1_001_create_users.sql          # Priority 1, Sequence 1
1_002_create_posts.sql          # Priority 1, Sequence 2
1_003_create_comments.pgsql     # Priority 1, Sequence 3
2_001_add_indexes.sql           # Priority 2, Sequence 1
2_002_add_constraints.sql       # Priority 2, Sequence 2
3_001_seed_users.sql            # Priority 3, Sequence 1
```

**Hyphen format:**
```
1-001-create-users.sql          # Priority 1, Sequence 1
1-002-create-posts.sql          # Priority 1, Sequence 2
1-003-create-comments.pgsql     # Priority 1, Sequence 3
10-10-create-newid.pgsql        # Priority 10, Sequence 10
```

**Mixed format (both in same directory):**
```
1_001_create_users.sql          # Priority 1, Sequence 1 (underscore)
1-002-create-posts.sql          # Priority 1, Sequence 2 (hyphen)
2_001_add_indexes.sql           # Priority 2, Sequence 1 (underscore)
```

**Execution Order**: 1→2→3→4→5→6 (sorted by Priority, then Sequence)

### Invalid Examples (Will be ignored)

```
migration.sql                   # Missing priority/sequence
create_users.sql                # Missing priority/sequence
1_create_users.sql              # Missing sequence
1_001_test.txt                  # Wrong extension
README.md                       # Not a SQL file
```

## Directory Structure

Scripts can be organized in subdirectories. The scanner recursively finds all matching SQL files:

```
migrations/
├── 1_001_create_schema.sql
├── 1_002_create_users.sql
├── tables/
│   ├── 1_003_create_posts.sql
│   └── 1_004_create_comments.pgsql
├── indexes/
│   └── 2_001_add_indexes.sql
└── data/
    └── 3_001_seed_data.sql
```

All files will be found and executed in Priority→Sequence order regardless of directory structure.

## External File Embedding

Script SQL can embed nearby text or binary files before execution using `-- @embed` directives:

```sql
-- @embed: path=assets/message.txt var=:message mode=text
-- @embed: path=assets/photo.bin var=:payload mode=base64
INSERT INTO assets (message, payload)
VALUES (:message, decode(:payload, 'base64')::bytea);
```

- `path`: File path resolved relative to the SQL file containing the directive
- `var`: Named placeholder to replace, such as `:message`
- `mode`: `text` embeds an escaped SQL string literal; `base64` embeds a base64 string literal

The directive comment is removed from the SQL, and every matching placeholder is replaced before the script is listed or executed.

## Commands

### relspec scripts list

List all SQL scripts in a directory and show their execution order.

**Usage:**
```bash
relspec scripts list --dir <directory> [flags]
```

**Flags:**
- `--dir <path>` (required): Directory containing SQL scripts
- `--schema <name>`: Schema name (default: "public")
- `--database <name>`: Database name (default: "database")

**Example:**
```bash
relspec scripts list --dir ./migrations
```

**Output:**
```
=== SQL Scripts List ===
Directory: ./migrations

Found 5 script(s) in execution order:

No.  Priority   Sequence Name                           Lines
---- --------   -------- ------------------------------ -----
1    1          1        create_users                   7
2    1          2        create_posts                   8
3    2          1        add_indexes                    4
4    2          2        add_constraints                6
5    3          1        seed_data                      4
```

### relspec scripts execute

Execute SQL scripts from a directory against a PostgreSQL database.

**Usage:**
```bash
relspec scripts execute --dir <directory> --conn <connection-string> [flags]
```

**Flags:**
- `--dir <path>` (required): Directory containing SQL scripts
- `--conn <string>` (required): PostgreSQL connection string
- `--schema <name>`: Schema name (default: "public")
- `--database <name>`: Database name (default: "database")

**Connection String Formats:**

```bash
# Standard PostgreSQL URLs
postgres://username:password@localhost:5432/database_name
postgres://username:password@localhost/database_name
postgresql://user:pass@host:5432/dbname?sslmode=disable
postgresql://user:pass@host/dbname?sslmode=require

# Key-value format
host=localhost port=5432 user=username password=pass dbname=mydb sslmode=disable
```

**Examples:**

```bash
# Execute migration scripts
relspec scripts execute \
  --dir ./migrations \
  --conn "postgres://user:pass@localhost:5432/mydb"

# Execute with custom schema
relspec scripts execute \
  --dir ./migrations \
  --conn "postgres://localhost/mydb" \
  --schema public

# Execute with SSL disabled
relspec scripts execute \
  --dir ./sql \
  --conn "postgres://user:pass@localhost/db?sslmode=disable"

# Execute using key-value connection string
relspec scripts execute \
  --dir ./migrations \
  --conn "host=localhost port=5432 user=admin password=secret dbname=prod"
```

**Output:**
```
=== SQL Scripts Execution ===
Started at: 2025-12-30 22:30:15
Directory: ./migrations
Database:  postgres://user:***@localhost:5432/mydb

[1/2] Reading SQL scripts...
      ✓ Found 4 script(s)

[2/2] Executing scripts in order (Priority → Sequence)...

Executing script: create_users (Priority=1, Sequence=1)
✓ Successfully executed: create_users
Executing script: create_posts (Priority=1, Sequence=2)
✓ Successfully executed: create_posts
Executing script: add_indexes (Priority=2, Sequence=1)
✓ Successfully executed: add_indexes
Executing script: seed_data (Priority=2, Sequence=2)
✓ Successfully executed: seed_data

=== Execution Complete ===
Completed at: 2025-12-30 22:30:16
Successfully executed 4 script(s)
```

## Execution Behavior

### Execution Order

Scripts are **always** executed in this order:
1. Sort by **Priority** (ascending)
2. Within same priority, sort by **Sequence** (ascending)

Example:
```
Priority 1, Sequence 1   → Executes 1st
Priority 1, Sequence 2   → Executes 2nd
Priority 1, Sequence 10  → Executes 3rd
Priority 2, Sequence 1   → Executes 4th
Priority 2, Sequence 5   → Executes 5th
Priority 10, Sequence 1  → Executes 6th
```

### Error Handling

- **Stop on First Error**: Execution stops immediately when any script fails
- **No Automatic Rollback**: Scripts executed before the failure remain committed
- **Error Details**: Full error message with script name, priority, and sequence

Example error output:
```
Executing script: add_indexes (Priority=2, Sequence=1)
Error: execution failed: failed to execute script add_indexes (Priority=2, Sequence=1):
       ERROR: syntax error at or near "IDNEX" (SQLSTATE 42601)
```

### Transaction Behavior

- Each script executes in its own implicit transaction (PostgreSQL default)
- No automatic transaction wrapping across multiple scripts
- For atomic migrations, manually wrap SQL in `BEGIN/COMMIT` blocks

### Empty Scripts

Scripts with empty SQL content are silently skipped.

## Use Cases

### Development Migrations

Organize database changes by priority levels:

```
migrations/
├── 1_xxx_schema.sql         # Priority 1: Core schema
├── 1_xxx_tables.sql
├── 2_xxx_indexes.sql        # Priority 2: Performance
├── 2_xxx_constraints.sql
└── 3_xxx_seed.sql           # Priority 3: Data
```

### Multi-Environment Deployments

Use priority levels for environment-specific scripts:

```
deploy/
├── 1_xxx_core_schema.sql    # Priority 1: All environments
├── 2_xxx_dev_data.sql       # Priority 2: Dev only
├── 2_xxx_staging_data.sql   # Priority 2: Staging only
└── 3_xxx_prod_data.sql      # Priority 3: Production only
```

### Incremental Rollouts

Use sequence for ordered feature rollouts:

```
features/
├── 1_001_feature_a_schema.sql
├── 1_002_feature_a_data.sql
├── 1_003_feature_b_schema.sql
├── 1_004_feature_b_data.sql
```

## Integration with RelSpec

The scripts command uses:
- **Reader**: `pkg/readers/sqldir/` - Reads SQL files into `models.Schema.Scripts`
- **Writer**: `pkg/writers/sqlexec/` - Executes scripts from `models.Schema.Scripts`

You can use these packages programmatically:

```go
import (
    "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"
)

// Read scripts
reader := sqldir.NewReader(&readers.ReaderOptions{
    FilePath: "./migrations",
})
db, _ := reader.ReadDatabase()

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

## Best Practices

### Naming

- Use zero-padded sequences: `001`, `002`, `010` (not `1`, `2`, `10`)
- Use descriptive names: `create_users_table`, not `table1`
- Group related changes: same priority for related DDL

### Organization

- Keep scripts small and focused (one logical change per file)
- Use priority levels to organize phases (schema → indexes → data)
- Document complex migrations with SQL comments

### Safety

- Always test migrations in development first
- Use `scripts list` to verify execution order before running
- Back up production databases before executing
- Consider using transactions for critical changes
- Review generated SQL before execution

### Version Control

- Commit scripts to version control
- Never modify executed scripts (create new ones instead)
- Use meaningful commit messages
- Tag releases with migration checkpoints

## Limitations

- PostgreSQL only (currently)
- No built-in rollback support
- No migration state tracking (no "already executed" detection)
- No dry-run mode
- Stops on first error (no partial execution tracking)

## Future Enhancements

Potential future features:
- Migration state tracking (executed scripts table)
- Rollback script support (using `models.Script.Rollback` field)
- Dry-run mode (validate without executing)
- Transaction wrapping (all-or-nothing execution)
- Multi-database support (MySQL, SQLite, etc.)
- Parallel execution for independent scripts
