`database/init.sh`

89 lines
#!/bin/bash

###############################################################################
# Database Initialization Script
# Creates PostgreSQL database and runs schema migrations
###############################################################################

set -e  # Exit on error

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

echo -e "${GREEN}═══════════════════════════════════════════════════════════${NC}"
echo -e "${GREEN}  Omega Watch Price History - Database Setup${NC}"
echo -e "${GREEN}═══════════════════════════════════════════════════════════${NC}\n"

# Load environment variables
if [ -f ../.env ]; then
    source ../.env
fi

# Database configuration
DB_NAME=${DB_NAME:-"omega_watches"}
DB_USER=${DB_USER:-"postgres"}
DB_HOST=${DB_HOST:-"localhost"}
DB_PORT=${DB_PORT:-5432}

echo -e "${YELLOW}Database Configuration:${NC}"
echo "  Database: $DB_NAME"
echo "  User:     $DB_USER"
echo "  Host:     $DB_HOST"
echo "  Port:     $DB_PORT"
echo ""

# Check if PostgreSQL is running
echo -e "${YELLOW}Checking PostgreSQL status...${NC}"
if ! pg_isready -h $DB_HOST -p $DB_PORT > /dev/null 2>&1; then
    echo -e "${RED}❌ PostgreSQL is not running on $DB_HOST:$DB_PORT${NC}"
    echo "Please start PostgreSQL and try again."
    exit 1
fi
echo -e "${GREEN}✅ PostgreSQL is running${NC}\n"

# Check if database exists
echo -e "${YELLOW}Checking if database exists...${NC}"
if psql -h $DB_HOST -p $DB_PORT -U $DB_USER -lqt | cut -d \| -f 1 | grep -qw $DB_NAME; then
    echo -e "${YELLOW}⚠️  Database '$DB_NAME' already exists${NC}"
    read -p "Do you want to drop and recreate it? (y/N) " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        echo -e "${YELLOW}Dropping database...${NC}"
        dropdb -h $DB_HOST -p $DB_PORT -U $DB_USER --if-exists $DB_NAME
        echo -e "${GREEN}✅ Database dropped${NC}"
    else
        echo -e "${YELLOW}Skipping database creation${NC}"
        exit 0
    fi
fi

# Create database
echo -e "${YELLOW}Creating database '$DB_NAME'...${NC}"
createdb -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME
echo -e "${GREEN}✅ Database created${NC}\n"

# Run schema
echo -e "${YELLOW}Running schema migration...${NC}"
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f schema.sql
echo -e "${GREEN}✅ Schema created${NC}\n"

# Verify schema
echo -e "${YELLOW}Verifying schema...${NC}"
TABLE_COUNT=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
echo -e "${GREEN}✅ Created $TABLE_COUNT tables${NC}\n"

# Show tables
echo -e "${YELLOW}Tables created:${NC}"
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "\dt"

echo -e "\n${GREEN}═══════════════════════════════════════════════════════════${NC}"
echo -e "${GREEN}  Database setup complete!${NC}"
echo -e "${GREEN}═══════════════════════════════════════════════════════════${NC}"
echo -e "\n${YELLOW}Next steps:${NC}"
echo "  1. Run migration: node database/migrate-json-to-postgres.js"
echo "  2. Start the server: npm start"
echo ""