# Schema Dump Command

## Overview

Add a `pgmold dump` command that exports a PostgreSQL database schema to SQL DDL format. This enables:
- Exporting existing database schemas to version control
- Creating baseline schema files for new projects
- Generating human-readable schema documentation

## CLI Interface

```bash
pgmold dump --database db:postgres://localhost/mydb [OPTIONS]
```

### Arguments

| Flag | Required | Description |
|------|----------|-------------|
| `--database` | Yes | Database connection string (format: `db:postgres://...`) |
| `--target-schemas` | No | Comma-separated list of schemas to dump (default: `public`) |
| `--output` / `-o` | No | Output file path (default: stdout) |

### Examples

```bash
# Dump public schema to stdout
pgmold dump --database db:postgres://localhost/mydb

# Dump specific schemas to file
pgmold dump --database db:postgres://localhost/mydb --target-schemas auth,api,public --output schema.sql

# Dump to file shorthand
pgmold dump --database db:postgres://localhost/mydb -o schema.sql
```

## Output Format

The dump produces valid PostgreSQL DDL in dependency order:

1. Extensions (`CREATE EXTENSION`)
2. Enums (`CREATE TYPE ... AS ENUM`)
3. Sequences without owners (`CREATE SEQUENCE`)
4. Tables (`CREATE TABLE` with columns, primary keys, check constraints)
5. Indexes (`CREATE INDEX`)
6. Foreign keys (`ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY`)
7. Sequence owners (`ALTER SEQUENCE ... OWNED BY`)
8. Functions (`CREATE FUNCTION`)
9. Views (`CREATE VIEW` / `CREATE MATERIALIZED VIEW`)
10. Row-Level Security (`ALTER TABLE ... ENABLE ROW LEVEL SECURITY`)
11. Policies (`CREATE POLICY`)
12. Triggers (`CREATE TRIGGER`)

### Output Characteristics

- Statements separated by `;\n\n` for readability
- All identifiers quoted (`"schema"."name"`)
- Deterministic ordering (alphabetical within each category)
- No transaction wrapping (user decides how to apply)
- Header comment with generation timestamp

### Example Output

```sql
-- Generated by pgmold dump
-- Database: postgres://localhost/mydb
-- Schemas: public
-- Generated at: 2025-12-05T12:00:00Z

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "public";

CREATE TYPE "public"."user_role" AS ENUM ('admin', 'member', 'guest');

CREATE TABLE "public"."users" (
    "id" bigint NOT NULL,
    "email" varchar(255) NOT NULL,
    "role" "public"."user_role" NOT NULL DEFAULT 'member',
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."posts" (
    "id" bigint NOT NULL,
    "user_id" bigint NOT NULL,
    "title" text NOT NULL,
    PRIMARY KEY ("id")
);

CREATE INDEX "posts_user_id_idx" ON "public"."posts" ("user_id");

ALTER TABLE "public"."posts"
    ADD CONSTRAINT "posts_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id")
    ON DELETE CASCADE;
```

## Implementation

### New Module: `src/dump.rs`

```rust
use crate::model::Schema;
use crate::diff::MigrationOp;
use crate::diff::planner::plan_migration;
use crate::pg::sqlgen::generate_sql;

/// Convert a Schema to CREATE operations for all objects
pub fn schema_to_create_ops(schema: &Schema) -> Vec<MigrationOp> {
    // Generate MigrationOp::Create* for each object in schema
}

/// Generate SQL dump from a Schema
pub fn generate_dump(schema: &Schema) -> String {
    let ops = schema_to_create_ops(schema);
    let planned = plan_migration(ops);
    let statements = generate_sql(&planned);
    statements.join(";\n\n") + ";\n"
}
```

### CLI Addition: `src/cli/mod.rs`

Add `Dump` variant to `Commands` enum:

```rust
#[derive(Subcommand)]
enum Commands {
    // ... existing commands ...

    /// Export database schema to SQL DDL
    Dump {
        /// Database connection string (format: db:postgres://...)
        #[arg(long)]
        database: String,

        /// Schemas to dump (comma-separated, default: public)
        #[arg(long, default_value = "public", value_delimiter = ',')]
        target_schemas: Vec<String>,

        /// Output file (default: stdout)
        #[arg(long, short)]
        output: Option<String>,
    },
}
```

## Test Cases

### Unit Tests (`src/dump.rs`)

```rust
#[cfg(test)]
mod tests {
    use super::*;
    use crate::parser::parse_sql_string;

    #[test]
    fn empty_schema_produces_empty_dump() {
        let schema = Schema::default();
        let ops = schema_to_create_ops(&schema);
        assert!(ops.is_empty());
    }

    #[test]
    fn single_table_dump() {
        let schema = parse_sql_string(r#"
            CREATE TABLE users (
                id BIGINT NOT NULL,
                email TEXT NOT NULL,
                PRIMARY KEY (id)
            );
        "#).unwrap();

        let ops = schema_to_create_ops(&schema);
        assert_eq!(ops.len(), 1);
        assert!(matches!(&ops[0], MigrationOp::CreateTable(t) if t.name == "users"));
    }

    #[test]
    fn dump_preserves_foreign_key_order() {
        // Tables with FK dependencies should be ordered correctly
        let schema = parse_sql_string(r#"
            CREATE TABLE posts (
                id BIGINT PRIMARY KEY,
                user_id BIGINT REFERENCES users(id)
            );
            CREATE TABLE users (
                id BIGINT PRIMARY KEY
            );
        "#).unwrap();

        let ops = schema_to_create_ops(&schema);
        let planned = plan_migration(ops);

        // users should come before posts due to FK dependency
        let user_idx = planned.iter().position(|op|
            matches!(op, MigrationOp::CreateTable(t) if t.name == "users")).unwrap();
        let post_idx = planned.iter().position(|op|
            matches!(op, MigrationOp::CreateTable(t) if t.name == "posts")).unwrap();
        assert!(user_idx < post_idx);
    }

    #[test]
    fn dump_includes_all_object_types() {
        let schema = parse_sql_string(r#"
            CREATE TYPE status AS ENUM ('active', 'inactive');
            CREATE SEQUENCE counter_seq;
            CREATE TABLE items (
                id BIGINT PRIMARY KEY,
                status status NOT NULL
            );
            CREATE INDEX items_status_idx ON items (status);
        "#).unwrap();

        let ops = schema_to_create_ops(&schema);

        assert!(ops.iter().any(|op| matches!(op, MigrationOp::CreateEnum(_))));
        assert!(ops.iter().any(|op| matches!(op, MigrationOp::CreateSequence(_))));
        assert!(ops.iter().any(|op| matches!(op, MigrationOp::CreateTable(_))));
        assert!(ops.iter().any(|op| matches!(op, MigrationOp::CreateIndex(_))));
    }

    #[test]
    fn dump_handles_multi_schema() {
        let schema = parse_sql_string(r#"
            CREATE TABLE auth.users (id BIGINT PRIMARY KEY);
            CREATE TABLE api.sessions (
                id BIGINT PRIMARY KEY,
                user_id BIGINT REFERENCES auth.users(id)
            );
        "#).unwrap();

        let dump = generate_dump(&schema);

        assert!(dump.contains(r#""auth"."users""#));
        assert!(dump.contains(r#""api"."sessions""#));
        assert!(dump.contains("REFERENCES"));
    }
}
```

### Integration Tests (`tests/integration.rs`)

```rust
#[tokio::test]
async fn dump_roundtrip() {
    // Create schema in DB -> dump -> parse dump -> diff should be empty
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    // Create some objects
    sqlx::query(r#"
        CREATE TYPE status AS ENUM ('active', 'inactive');
        CREATE TABLE users (
            id BIGINT PRIMARY KEY,
            email TEXT NOT NULL,
            status status DEFAULT 'active'
        );
        CREATE INDEX users_email_idx ON users (email);
    "#).execute(connection.pool()).await.unwrap();

    // Introspect
    let schema = introspect_schema(&connection, &["public".to_string()]).await.unwrap();

    // Dump to SQL
    let dump = generate_dump(&schema);

    // Parse the dump
    let parsed = parse_sql_string(&dump).unwrap();

    // Diff should be empty (roundtrip successful)
    let diff = compute_diff(&schema, &parsed);
    assert!(diff.is_empty(), "Roundtrip diff should be empty: {diff:?}");
}

#[tokio::test]
async fn dump_multi_schema_roundtrip() {
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    sqlx::query("CREATE SCHEMA auth").execute(connection.pool()).await.unwrap();
    sqlx::query(r#"
        CREATE TABLE auth.users (id BIGINT PRIMARY KEY, email TEXT NOT NULL);
        CREATE TABLE public.posts (
            id BIGINT PRIMARY KEY,
            user_id BIGINT REFERENCES auth.users(id)
        );
    "#).execute(connection.pool()).await.unwrap();

    let schema = introspect_schema(&connection, &["public".to_string(), "auth".to_string()]).await.unwrap();
    let dump = generate_dump(&schema);
    let parsed = parse_sql_string(&dump).unwrap();

    let diff = compute_diff(&schema, &parsed);
    assert!(diff.is_empty(), "Multi-schema roundtrip diff: {diff:?}");
}

#[tokio::test]
async fn dump_complex_schema() {
    // Test with functions, views, triggers, policies
    let (_container, url) = setup_postgres().await;
    let connection = PgConnection::new(&url).await.unwrap();

    sqlx::query(r#"
        CREATE TABLE users (id BIGINT PRIMARY KEY, email TEXT);

        CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
            SELECT COUNT(*)::INTEGER FROM users;
        $$ LANGUAGE SQL STABLE;

        CREATE VIEW active_users AS SELECT * FROM users WHERE id > 0;

        ALTER TABLE users ENABLE ROW LEVEL SECURITY;

        CREATE POLICY users_select ON users FOR SELECT USING (true);
    "#).execute(connection.pool()).await.unwrap();

    let schema = introspect_schema(&connection, &["public".to_string()]).await.unwrap();
    let dump = generate_dump(&schema);

    // Verify dump contains all object types
    assert!(dump.contains("CREATE TABLE"));
    assert!(dump.contains("CREATE FUNCTION") || dump.contains("CREATE OR REPLACE FUNCTION"));
    assert!(dump.contains("CREATE VIEW") || dump.contains("CREATE OR REPLACE VIEW"));
    assert!(dump.contains("ENABLE ROW LEVEL SECURITY"));
    assert!(dump.contains("CREATE POLICY"));
}
```

## Implementation Plan

### Phase 1: Core dump module
1. Create `src/dump.rs` with `schema_to_create_ops()` function
2. Implement conversion for each object type:
   - Extensions -> `CreateExtension`
   - Enums -> `CreateEnum`
   - Sequences -> `CreateSequence`
   - Tables -> `CreateTable`
   - Indexes -> `CreateIndex`
   - Foreign Keys -> `AddForeignKey`
   - Check Constraints -> `AddCheckConstraint`
   - Functions -> `CreateFunction`
   - Views -> `CreateView`
   - RLS -> `EnableRls`
   - Policies -> `CreatePolicy`
   - Triggers -> `CreateTrigger`
3. Implement `generate_dump()` function
4. Add unit tests

### Phase 2: CLI integration
1. Add `Dump` command variant to `Commands` enum
2. Implement handler in `run()` function
3. Add header comment generation with metadata
4. Handle `--output` file writing

### Phase 3: Integration tests
1. Add roundtrip test (dump -> parse -> diff = empty)
2. Add multi-schema roundtrip test
3. Add complex schema test (all object types)

### Phase 4: Polish
1. Add `--no-header` flag option
2. Consider `--single-transaction` wrapper option
3. Update CLI help text and README

## Edge Cases

1. **Empty schema**: Should produce empty output (or header-only)
2. **Circular FK dependencies**: Rely on planner's topological sort
3. **Non-ASCII identifiers**: Already handled by `quote_ident()`
4. **Large function bodies**: Preserved as-is with dollar-quoting
5. **Materialized views**: Should use `CREATE MATERIALIZED VIEW`
6. **Extension dependencies**: Extensions must come first
