# Snowflake to SQL Migration Script

This script generates SQL INSERT statements from Snowflake tables for data migration purposes. It's specifically designed to handle complex relationships between tables, particularly for contract-related data.

## Prerequisites

- Python 3.x
- Required Python packages:
  - snowflake-connector-python
  - cryptography
  - pandas
  - csv
  - json
  - datetime
  - pathlib

## Installation

1. Clone the repository
2. Install required packages:
```bash
pip install snowflake-connector-python cryptography pandas
```

## Configuration

### Snowflake Connection
The script requires Snowflake credentials and a private key for authentication. Set up your environment with:

1. Private key file at `~/.ssh/snowflake/rsa_key.p8`
2. Environment variable `PRIVATE_KEY_PASSPHRASE` for the private key password

### Column Configuration
Create a JSON configuration file (`table_columns_config.json`) to specify which columns to include for each table. Example format:

```json
{
    "account": ["account_id", "account_name", "created_at", "created_by", "last_modified", "last_modified_by", "sap_created_at"],
    "contract": ["contract_id", "contract_name", "status", "created_at"]
}
```

If no configuration is provided, the script will use `SELECT *` for all tables.

## Usage

### Running the Script

1. Basic usage:
```bash
python generate_sql_script_sf.py
```

2. Using with an existing cursor:
```python
from generate_sql_script_sf import main_with_cursor
main_with_cursor(cs, config_file_path="path/to/config.json")
```

### Input Prompts

The script will prompt for:
1. Path to column configuration file (defaults to `table_columns_config.json` in script directory)
2. Path to save SQL files (defaults to script directory)
3. Path to CSV files directory (when processing contract terms)

### Output

The script generates SQL files in the specified output directory with the following naming convention:
- Regular tables: `20-{order_num}-migration-contracts_{table_name}_qa.sql`
- Combined contract terms: `20-09-migration-contracts_combined_contract_terms_qa.sql`

## Tables Processed

The script handles the following tables:
1. ACCOUNT
2. ACCOUNT_PAYMENT_TERM
3. ACCOUNT_PAYEE
4. ACCOUNT_TAX_INFO
5. CONTRACT
6. RUN_CONTROLLER_CONTRACT
7. LEGACY_CONTRACT
8. ACCOUNT_CONTRACT
9. CONTRACT_TERM and CONTRACT_TERM_CONDITION (special handling)

## Special Features

### Contract Terms Handling
The script includes special handling for CONTRACT_TERM and CONTRACT_TERM_CONDITION tables:
- Maintains parent-child relationships
- Uses LAST_INSERT_ID() for proper relationship mapping
- Handles JSON data in conditions column
- Processes data in batches for better performance

### Data Cleaning
- Handles NULL values appropriately
- Escapes special characters in strings
- Formats dates and timestamps
- Cleans and validates JSON data
- Handles multiline strings

### Error Handling
- Comprehensive error handling and logging
- Graceful handling of missing configuration files
- Detailed error messages for troubleshooting

## Security Considerations

1. Private key handling:
   - Uses secure key storage
   - Requires passphrase for key decryption
   - Follows best practices for key management

2. Data handling:
   - Proper escaping of special characters
   - Secure handling of sensitive data
   - Input validation and sanitization

## Troubleshooting

Common issues and solutions:

1. Connection errors:
   - Verify Snowflake credentials
   - Check private key file location and permissions
   - Ensure environment variables are set correctly

2. Data formatting issues:
   - Check JSON data format in source tables
   - Verify column configurations
   - Review error logs for specific issues

3. File permission issues:
   - Ensure write permissions in output directory
   - Check file permissions for configuration files

## Contributing

Feel free to submit issues and enhancement requests!

## License

[Specify your license here]