hr-portal/scripts/setup-hr-portal-db.sh

#!/bin/bash
#
# HR Portal Database Setup Script
# Run this script on Ubuntu Server (10.1.0.254)
#
# Usage: bash setup-hr-portal-db.sh
#

set -e

echo "========================================"
echo "  HR Portal Database Setup"
echo "========================================"
echo ""

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

# Configuration
DB_NAME="hr_portal"
DB_USER="hr_user"
DB_PASSWORD=""
POSTGRES_CONTAINER="postgres"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

# Step 1: Check PostgreSQL container
echo -e "${YELLOW}[1/6] Checking PostgreSQL container...${NC}"

if docker ps | grep -q "$POSTGRES_CONTAINER"; then
    echo -e "${GREEN}  ✓ PostgreSQL container is running${NC}"
    docker ps --filter "name=$POSTGRES_CONTAINER" --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}"
else
    echo -e "${RED}  ✗ PostgreSQL container not found or not running${NC}"
    echo ""
    echo "Available containers:"
    docker ps -a | grep -i postgres || echo "No PostgreSQL containers found"
    echo ""
    read -p "Enter PostgreSQL container name: " POSTGRES_CONTAINER
fi

echo ""

# Step 2: Prompt for passwords
echo -e "${YELLOW}[2/6] Setting up credentials...${NC}"

read -sp "Enter password for database user 'hr_user': " DB_PASSWORD
echo ""

if [ -z "$DB_PASSWORD" ]; then
    echo -e "${RED}  ✗ Password cannot be empty${NC}"
    exit 1
fi

echo -e "${GREEN}  ✓ Credentials configured${NC}"
echo ""

# Step 3: Create database user
echo -e "${YELLOW}[3/6] Creating database user '$DB_USER'...${NC}"

# Check if user exists
USER_EXISTS=$(docker exec -i $POSTGRES_CONTAINER psql -U postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='$DB_USER'" 2>&1)

if echo "$USER_EXISTS" | grep -q "1"; then
    echo -e "${YELLOW}  ⚠ User '$DB_USER' already exists, skipping...${NC}"
else
    docker exec -i $POSTGRES_CONTAINER psql -U postgres <<EOF
CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';
ALTER USER $DB_USER WITH SUPERUSER;
EOF

    if [ $? -eq 0 ]; then
        echo -e "${GREEN}  ✓ User '$DB_USER' created successfully${NC}"
    else
        echo -e "${RED}  ✗ Failed to create user${NC}"
        exit 1
    fi
fi

echo ""

# Step 4: Create database
echo -e "${YELLOW}[4/6] Creating database '$DB_NAME'...${NC}"

# Check if database exists
DB_EXISTS=$(docker exec -i $POSTGRES_CONTAINER psql -U postgres -lqt | cut -d \| -f 1 | grep -w "$DB_NAME" | wc -l)

if [ "$DB_EXISTS" -gt 0 ]; then
    echo -e "${YELLOW}  ⚠ Database '$DB_NAME' already exists${NC}"
    read -p "  Do you want to drop and recreate it? (yes/no): " CONFIRM

    if [ "$CONFIRM" = "yes" ]; then
        echo "  Dropping existing database..."
        docker exec -i $POSTGRES_CONTAINER psql -U postgres -c "DROP DATABASE $DB_NAME;"
        echo "  Creating new database..."
        docker exec -i $POSTGRES_CONTAINER psql -U postgres -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;"
        echo -e "${GREEN}  ✓ Database recreated${NC}"
    else
        echo -e "${YELLOW}  ⚠ Skipping database creation${NC}"
    fi
else
    docker exec -i $POSTGRES_CONTAINER psql -U postgres <<EOF
CREATE DATABASE $DB_NAME OWNER $DB_USER;
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
EOF

    if [ $? -eq 0 ]; then
        echo -e "${GREEN}  ✓ Database '$DB_NAME' created successfully${NC}"
    else
        echo -e "${RED}  ✗ Failed to create database${NC}"
        exit 1
    fi
fi

echo ""

# Step 5: Initialize schema
echo -e "${YELLOW}[5/6] Initializing database schema...${NC}"

if [ -f "$SCRIPT_DIR/init-db.sql" ]; then
    echo "  Executing init-db.sql..."

    docker exec -i $POSTGRES_CONTAINER psql -U $DB_USER -d $DB_NAME < "$SCRIPT_DIR/init-db.sql"

    if [ $? -eq 0 ]; then
        echo -e "${GREEN}  ✓ Schema initialized successfully${NC}"
    else
        echo -e "${RED}  ✗ Failed to initialize schema${NC}"
        exit 1
    fi
else
    echo -e "${RED}  ✗ init-db.sql not found in $SCRIPT_DIR${NC}"
    exit 1
fi

echo ""

# Step 6: Verify setup
echo -e "${YELLOW}[6/6] Verifying database setup...${NC}"

# Count tables
TABLE_COUNT=$(docker exec -i $POSTGRES_CONTAINER psql -U $DB_USER -d $DB_NAME -tAc "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';")

echo "  Tables created: $TABLE_COUNT"

if [ "$TABLE_COUNT" -ge 9 ]; then
    echo -e "${GREEN}  ✓ Database setup verified${NC}"
else
    echo -e "${YELLOW}  ⚠ Expected 9 tables, found $TABLE_COUNT${NC}"
fi

# List tables
echo ""
echo "  Tables in database:"
docker exec -i $POSTGRES_CONTAINER psql -U $DB_USER -d $DB_NAME -c "\dt"

echo ""
echo -e "${GREEN}========================================"
echo "  Setup Completed Successfully!"
echo "========================================${NC}"
echo ""
echo "Database Connection String:"
echo -e "${CYAN}postgresql://$DB_USER:$DB_PASSWORD@10.1.0.254:5432/$DB_NAME${NC}"
echo ""
echo "Next steps:"
echo "  1. Update backend/.env with the connection string"
echo "  2. Run insert-test-data.sql to add sample data"
echo "  3. Test backend database connection"
echo ""