Goa-gel-fullstack/backend/scripts/db-reset.sh

#!/bin/bash
set -e

echo "========================================"
echo " Goa-GEL Database Reset"
echo "========================================"
echo ""

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Check if running in Docker or locally
if [ -z "$DATABASE_HOST" ]; then
  # Load from .env file if not in Docker
  if [ -f ".env" ]; then
    set -a
    source .env
    set +a
  fi

  # Default values for local development
  DATABASE_HOST=${DATABASE_HOST:-localhost}
  DATABASE_PORT=${DATABASE_PORT:-5432}
  DATABASE_NAME=${DATABASE_NAME:-goa_gel_platform}
  DATABASE_USER=${DATABASE_USER:-postgres}
  DATABASE_PASSWORD=${DATABASE_PASSWORD:-postgres}
fi

echo -e "${YELLOW}WARNING: This will delete ALL data in the database!${NC}"
echo ""
echo "Database: $DATABASE_NAME @ $DATABASE_HOST:$DATABASE_PORT"
echo ""

# Check for --force flag
if [ "$1" != "--force" ] && [ "$1" != "-f" ]; then
  read -p "Are you sure you want to continue? (y/N) " -n 1 -r
  echo ""
  if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo "Aborted."
    exit 0
  fi
fi

echo ""
echo "[1/4] Connecting to PostgreSQL..."

# Test connection
if ! PGPASSWORD=$DATABASE_PASSWORD psql -h "$DATABASE_HOST" -U "$DATABASE_USER" -d "$DATABASE_NAME" -c '\q' 2>/dev/null; then
  echo -e "${RED}ERROR: Cannot connect to PostgreSQL${NC}"
  exit 1
fi
echo -e "${GREEN}  - Connected successfully${NC}"

echo ""
echo "[2/4] Dropping all tables..."

# Drop all tables by using a transaction
PGPASSWORD=$DATABASE_PASSWORD psql -h "$DATABASE_HOST" -U "$DATABASE_USER" -d "$DATABASE_NAME" <<EOF
-- Disable triggers
SET session_replication_role = replica;

-- Drop all tables in dependency order
DROP TABLE IF EXISTS application_logs CASCADE;
DROP TABLE IF EXISTS blockchain_events CASCADE;
DROP TABLE IF EXISTS blockchain_transactions CASCADE;
DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS webhook_logs CASCADE;
DROP TABLE IF EXISTS webhooks CASCADE;
DROP TABLE IF EXISTS workflow_states CASCADE;
DROP TABLE IF EXISTS approvals CASCADE;
DROP TABLE IF EXISTS document_versions CASCADE;
DROP TABLE IF EXISTS documents CASCADE;
DROP TABLE IF EXISTS license_requests CASCADE;
DROP TABLE IF EXISTS wallets CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS applicants CASCADE;
DROP TABLE IF EXISTS workflows CASCADE;
DROP TABLE IF EXISTS departments CASCADE;
DROP TABLE IF EXISTS knex_migrations CASCADE;
DROP TABLE IF EXISTS knex_migrations_lock CASCADE;

-- Re-enable triggers
SET session_replication_role = DEFAULT;

-- Vacuum to reclaim space
VACUUM;
EOF

echo -e "${GREEN}  - All tables dropped${NC}"

echo ""
echo "[3/4] Running migrations..."

# Determine the correct directory for knexfile
if [ -f "/app/src/database/knexfile.js" ]; then
  # Docker environment
  cd /app/src/database
elif [ -f "src/database/knexfile.ts" ]; then
  # Local development
  cd src/database
else
  echo -e "${RED}ERROR: Cannot find knexfile${NC}"
  exit 1
fi

# Run migrations
node -e "
const knex = require('knex');
const path = require('path');

// Try to load compiled JS first, then TypeScript
let config;
try {
  config = require('./knexfile.js').default || require('./knexfile.js');
} catch (e) {
  require('ts-node/register');
  config = require('./knexfile.ts').default || require('./knexfile.ts');
}

const env = process.env.NODE_ENV || 'production';
const db = knex(config[env] || config.development);

db.migrate.latest()
  .then(([batchNo, migrations]) => {
    console.log('  - Applied ' + migrations.length + ' migration(s)');
    migrations.forEach(m => console.log('    * ' + m));
    process.exit(0);
  })
  .catch(err => {
    console.error('  - Migration failed:', err.message);
    process.exit(1);
  })
  .finally(() => db.destroy());
"

echo -e "${GREEN}  - Migrations completed${NC}"

echo ""
echo "[4/4] Running seeds..."

node -e "
const knex = require('knex');

// Try to load compiled JS first, then TypeScript
let config;
try {
  config = require('./knexfile.js').default || require('./knexfile.js');
} catch (e) {
  require('ts-node/register');
  config = require('./knexfile.ts').default || require('./knexfile.ts');
}

const env = process.env.NODE_ENV || 'production';
const db = knex(config[env] || config.development);

db.seed.run()
  .then(() => {
    console.log('  - Seeds completed successfully');
    process.exit(0);
  })
  .catch(err => {
    console.error('  - Seed failed:', err.message);
    process.exit(1);
  })
  .finally(() => db.destroy());
"

echo -e "${GREEN}  - Seeds completed${NC}"

echo ""
echo "========================================"
echo -e "${GREEN} Database Reset Complete!${NC}"
echo "========================================"
echo ""
echo "Demo Accounts:"
echo "  Admin:        admin@goa.gov.in / Admin@123"
echo "  Fire Dept:    fire@goa.gov.in / Fire@123"
echo "  Tourism:      tourism@goa.gov.in / Tourism@123"
echo "  Municipality: municipality@goa.gov.in / Municipality@123"
echo "  Citizen 1:    citizen@example.com / Citizen@123"
echo "  Citizen 2:    citizen2@example.com / Citizen@123"
echo ""