/safe-migrate create add_user_preferences     # Create new migration
/safe-migrate analyze [migration-file]        # Analyze for risks
/safe-migrate apply [migration-file]          # Apply with safety checks
/safe-migrate apply --production              # Apply to production (extra checks)
/safe-migrate rollback [migration-file]       # Generate rollback migration
/safe-migrate backup [table-name]             # Backup specific table

# Always run from root directory
cd /Users/sungholee/code/kstorybridge

# Create migration with timestamp
npx supabase migration new [migration_name]

-- Migration: [timestamp]_[name].sql
-- Created: YYYY-MM-DD
-- Author: [name]
-- Status: IN_PROGRESS | COMPLETED | DEPRECATED
--
-- Description:
-- [What this migration does]
--
-- Risk Level: LOW | MEDIUM | HIGH | CRITICAL
-- Destructive: YES | NO
--
-- Affected Tables:
-- - [table_name] ([row count] rows)
--
-- Backup Required: YES | NO
-- Backup Created: [backup file path]
--
-- Rollback Procedure:
-- [How to undo this migration]
--
-- Testing:
-- [ ] Tested locally
-- [ ] Tested in staging
-- [ ] Verified row counts
-- [ ] Verified data integrity

# Create backup directory if needed
mkdir -p supabase/backups

# Get row count before backup
psql "$DATABASE_URL" -c "SELECT COUNT(*) FROM [table_name];"

# Export table to backup file
pg_dump "$DATABASE_URL" \
  --table=[table_name] \
  --data-only \
  --format=custom \
  --file=supabase/backups/[table_name]_$(date +%Y%m%d_%H%M%S).dump

# Or using SQL export
psql "$DATABASE_URL" -c "COPY [table_name] TO STDOUT WITH CSV HEADER" \
  > supabase/backups/[table_name]_$(date +%Y%m%d_%H%M%S).csv

-- Rollback: [timestamp]_[name]_rollback.sql
-- Reverts: [timestamp]_[name].sql
--
-- Instructions:
-- 1. Apply this migration to revert changes
-- 2. Verify data integrity after rollback
-- 3. Check application functionality

-- Rollback SQL here

-- Adding a column is always safe
ALTER TABLE users
ADD COLUMN preferences JSONB DEFAULT '{}';

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN new_name TEXT;

-- Step 2: Copy data
UPDATE users SET new_name = old_name;

-- Step 3: Add NOT NULL if needed (after data migration)
ALTER TABLE users ALTER COLUMN new_name SET NOT NULL;

-- Step 4: Drop old column (in SEPARATE migration after verification)
-- ALTER TABLE users DROP COLUMN old_name;

-- NEVER do this directly:
-- ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

-- INSTEAD:
-- Step 1: Add new column with new type
ALTER TABLE users ADD COLUMN age_int INTEGER;

-- Step 2: Migrate data
UPDATE users SET age_int = CAST(age AS INTEGER) WHERE age ~ '^\d+$';

-- Step 3: Verify data
-- SELECT COUNT(*) FROM users WHERE age_int IS NULL AND age IS NOT NULL;

-- Step 4: Rename columns (in SEPARATE migration)
-- ALTER TABLE users RENAME COLUMN age TO age_old;
-- ALTER TABLE users RENAME COLUMN age_int TO age;

-- Step 5: Drop old column (in SEPARATE migration)
-- ALTER TABLE users DROP COLUMN age_old;

-- NEVER use DROP TABLE directly!

-- INSTEAD:
-- Step 1: Rename to deprecated
ALTER TABLE old_table RENAME TO _deprecated_old_table;

-- Step 2: Wait 30 days for issues to surface

-- Step 3: Create backup before final drop
-- pg_dump --table=_deprecated_old_table ...

-- Step 4: Drop in separate migration (after 30+ days)
-- DROP TABLE IF EXISTS _deprecated_old_table;

# Reset local database and apply all migrations
npx supabase db reset

# Check migration status
npx supabase migration list

# Push to staging (linked project)
npx supabase db push --linked

# Verify application
npx supabase migration list --linked

# Push to production
npx supabase db push

# Verify immediately after
npx supabase migration list

# If rollback migration exists
npx supabase migration new rollback_[original_name]

# Apply rollback SQL
npx supabase db push

# Restore from pg_dump backup
pg_restore --data-only \
  --table=[table_name] \
  --dbname="$DATABASE_URL" \
  supabase/backups/[table_name]_[timestamp].dump

# Or from CSV backup
psql "$DATABASE_URL" -c "\COPY [table_name] FROM 'backup.csv' WITH CSV HEADER"

## Migration Analysis Report

**File**: 20251225000000_add_preferences.sql
**Lines**: 45

### Risk Assessment

**Risk Level**: MEDIUM
**Destructive Operations**: YES

### Detected Operations

| Line | Operation | Table | Risk |
|------|-----------|-------|------|
| 12 | ALTER TABLE ADD | users | LOW |
| 18 | UPDATE SET | users | MEDIUM |
| 25 | DROP COLUMN | users | HIGH |

### Tables Affected

| Table | Rows | Operation |
|-------|------|-----------|
| users | 1,234 | MODIFY |

### Recommendations

1. CREATE BACKUP before running (users table has 1,234 rows)
2. Test in staging first
3. Consider splitting DROP COLUMN into separate migration
4. Add WHERE clause to UPDATE if possible

### Pre-flight Checklist

- [ ] Backup created for: users
- [ ] Tested locally with `npx supabase db reset`
- [ ] Tested in staging with production-like data
- [ ] Row counts verified before/after
- [ ] Rollback migration prepared
- [ ] Team notified of migration window

Analyzing migration: 20251225000000_add_preferences.sql

DESTRUCTIVE OPERATIONS DETECTED

  Line 25: DROP COLUMN users.old_field
  Risk: HIGH

BACKUP REQUIRED

Before proceeding, create a backup:
  /safe-migrate backup users

Or run: pg_dump --table=users ...

Continue? (requires --force flag for destructive operations)

{
  "text": "Migration Applied: 20251225000000_add_preferences.sql",
  "attachments": [
    {
      "color": "warning",
      "fields": [
        {"title": "Risk Level", "value": "MEDIUM", "short": true},
        {"title": "Tables", "value": "users", "short": true},
        {"title": "Backup", "value": "users_20251225.dump", "short": true},
        {"title": "Status", "value": "SUCCESS", "short": true}
      ]
    }
  ]
}

-- Migration: [timestamp]_[name].sql
-- Risk Level: [LOW/MEDIUM/HIGH]
-- Backup: [path or N/A]

-- Pre-migration verification
DO $$
DECLARE
  row_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO row_count FROM [table_name];
  RAISE NOTICE 'Pre-migration row count for [table_name]: %', row_count;
END $$;

-- Migration statements
[SQL HERE]

-- Post-migration verification
DO $$
DECLARE
  row_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO row_count FROM [table_name];
  RAISE NOTICE 'Post-migration row count for [table_name]: %', row_count;
END $$;

-- Migration: [timestamp]_[name].sql
-- Risk Level: HIGH/CRITICAL
-- Backup Required: YES
-- Backup Path: supabase/backups/[table]_[timestamp].dump

-- IMPORTANT: Ensure backup exists before running
-- Run: /safe-migrate backup [table_name]

-- Verify backup exists (this will fail if backup is missing)
-- DO $$
-- BEGIN
--   IF NOT EXISTS (SELECT 1 FROM pg_stat_file('supabase/backups/[backup_file]')) THEN
--     RAISE EXCEPTION 'Backup file not found. Create backup before proceeding.';
--   END IF;
-- END $$;

-- Pre-migration state
[verification SQL]

-- Destructive operation
[SQL HERE]

-- Post-migration verification
[verification SQL]

-- Rollback instructions in comments
-- To rollback: pg_restore --table=[table] supabase/backups/[backup_file]