#!/bin/bash

# ==========================================================================
# SCRIPT: rds_execute_sql.sh
# DESCRIPTION: Executes SQL statements from a local .sql file on an Amazon RDS
#              database using the AWS RDS Data API. This method is particularly
#              useful for serverless databases like Aurora Serverless, but also
#              works with provisioned RDS instances, providing a way to execute
#              SQL without a direct database connection.
#
# USE CASE SCENARIO:
# A data engineering team needs to apply a new database schema, load seed data,
# or run maintenance scripts on an RDS instance. This script automates the
# execution of SQL scripts against the database using the RDS Data API.
#
# PREREQUISITES:
# 1.  **AWS CLI:** The AWS Command Line Interface must be installed and configured
#     with credentials that have the necessary permissions.
# 2.  **IAM Permissions:** The principal executing this script must have:
#     - `rds-data:ExecuteStatement` on the target RDS DB cluster/instance.
#     - `secretsmanager:GetSecretValue` on the Secrets Manager secret containing DB credentials.
# 3.  **Existing Resources:**
#     - An Amazon RDS DB cluster or instance (e.g., Aurora Serverless, PostgreSQL, MySQL).
#     - A Secrets Manager secret containing the database credentials (username and password).
#     - A local .sql file containing the SQL statements to be executed.
#
# HOW TO USE:
# 1.  **Save the script:** Save this content as `rds_execute_sql.sh`.
# 2.  **Make it executable:** `chmod +x rds_execute_sql.sh`
# 3.  **Configure variables:** Open the script and update the `--- Configuration Variables ---`
#     section with your specific environment details.
# 4.  **Prepare your SQL file:** Create a .sql file (e.g., `schema.sql`)
#     containing the SQL statements. Example:
#     `CREATE TABLE IF NOT EXISTS products (id SERIAL PRIMARY KEY, name VARCHAR(255), price DECIMAL(10, 2));`
#     `INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);`
# 5.  **Run from your terminal:** `./rds_execute_sql.sh`
#
# IMPORTANT CONSIDERATIONS:
# - The RDS Data API is generally preferred for serverless applications (e.g., Lambda)
#   or when you want to avoid managing direct database connections.
# - Ensure the Secrets Manager secret contains the correct database credentials.
# - For very large SQL files or complex migrations, consider using database-specific
#   migration tools (e.g., Flyway, Liquibase) or AWS DMS.
# ==========================================================================

# --- Configuration Variables (REPLACE with your actual values) ---
DB_CLUSTER_ARN="arn:aws:rds:us-east-1:123456789012:cluster:my-aurora-serverless-cluster" # ARN of the RDS DB Cluster (e.g., Aurora Serverless) or DB Instance
DB_SECRET_ARN="arn:aws:secretsmanager:us-east-1:123456789012:secret:my/db/credentials" # ARN of the Secrets Manager secret for DB credentials
DB_NAME="mydb"                                                               # Name of the database to connect to
SQL_FILE_PATH="/path/to/your/schema.sql"                                         # Path to the local SQL file containing the statements
AWS_REGION="us-east-1"                                                         # AWS region
# ----------------------------------------------------------------

echo "Starting SQL execution from '${SQL_FILE_PATH}' on RDS database '${DB_NAME}' using AWS RDS Data API in region ${AWS_REGION}...\n"

# ==========================================================================
# STEP 1: Validate the existence of the local SQL file.
# The script cannot proceed if the SQL file does not exist.
# ==========================================================================
echo ">>> Step 1: Checking for local SQL file: '${SQL_FILE_PATH}'...
"
if [ ! -f "${SQL_FILE_PATH}" ]; then
    echo "Error: SQL file not found at '${SQL_FILE_PATH}'. Please ensure the file exists. Exiting.\n"
    exit 1
fi
echo "   Local SQL file '${SQL_FILE_PATH}' found.\n"

# ==========================================================================
# STEP 2: Read SQL statements from the file.
# The entire content of the file is read as a single string.
# ==========================================================================
echo ">>> Step 2: Reading SQL statements from '${SQL_FILE_PATH}'...
"
SQL_STATEMENTS=$(cat "${SQL_FILE_PATH}")

if [ -z "${SQL_STATEMENTS}" ]; then
    echo "Error: SQL file '${SQL_FILE_PATH}' is empty. No statements to execute. Exiting.\n"
    exit 1
fi
echo "   Successfully loaded SQL statements from '${SQL_FILE_PATH}'.\n"

# ==========================================================================
# STEP 3: Execute the SQL statements on the RDS database using the RDS Data API.
# This API allows executing SQL without a direct database connection.
# ==========================================================================
echo ">>> Step 3: Executing SQL statements on database '${DB_NAME}'...
"
aws rds-data execute-statement \
    --resource-arn "${DB_CLUSTER_ARN}" \
    --secret-arn "${DB_SECRET_ARN}" \
    --database "${DB_NAME}" \
    --sql "${SQL_STATEMENTS}" \
    --region "${AWS_REGION}" || { echo "Error: AWS RDS Data API execute-statement failed. Exiting.\n"; exit 1; }

# Check the exit code of the last command.
if [ $? -eq 0 ]; then
    echo "SQL statements executed successfully.\n"
else
    echo "Error: Failed to execute SQL statements. Please check the logs above for details.\n"
    exit 1
fi

echo "=== RDS SQL execution completed. ===\n"