#!/bin/bash

# Run this script on your production server to apply the backup_jobs table migration
# to all library databases

echo "Applying backup_jobs table migration to all databases..."
echo ""

# Apply to each database
for DB in storycove storycove_afterdark storycove_clas storycove_secret; do
    echo "Applying to $DB..."
    docker-compose exec -T postgres psql -U storycove -d "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS backup_jobs (
    id UUID PRIMARY KEY,
    library_id VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL CHECK (type IN ('DATABASE_ONLY', 'COMPLETE')),
    status VARCHAR(50) NOT NULL CHECK (status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED', 'EXPIRED')),
    file_path VARCHAR(1000),
    file_size_bytes BIGINT,
    progress_percent INTEGER,
    error_message VARCHAR(1000),
    created_at TIMESTAMP NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    expires_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_backup_jobs_library_id ON backup_jobs(library_id);
CREATE INDEX IF NOT EXISTS idx_backup_jobs_status ON backup_jobs(status);
CREATE INDEX IF NOT EXISTS idx_backup_jobs_expires_at ON backup_jobs(expires_at);
CREATE INDEX IF NOT EXISTS idx_backup_jobs_created_at ON backup_jobs(created_at DESC);
SQL
    echo "✓ Done with $DB"
    echo ""
done

echo "Migration complete! Verifying..."
echo ""

# Verify tables exist
for DB in storycove storycove_afterdark storycove_clas storycove_secret; do
    echo "Checking $DB:"
    docker-compose exec -T postgres psql -U storycove -d "$DB" -c "\d backup_jobs" 2>&1 | grep -E "Table|does not exist" || echo "  ✓ Table exists"
    echo ""
done