ferrero-naming-tool/deploy-database-linux.sh

#!/bin/bash
##############################################################################
# Ferrero Asset Tracking Database - Linux Production Deployment Script
#
# This script deploys the PostgreSQL tracking database on a Linux server.
# The database is a standalone service used by multiple applications.
#
# Usage:
#   sudo ./deploy-database-linux.sh
#
# Requirements:
#   - Linux (Ubuntu 20.04+ / CentOS 8+ / Debian 11+)
#   - Docker and Docker Compose installed
#   - Root or sudo access
#   - Minimum 2GB RAM, 10GB disk space
##############################################################################

set -e  # Exit on error

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

# Configuration - Edit these for production
DB_NAME="${DB_NAME:-ferrero_tracking}"
DB_USER="${DB_USER:-ferrero_user}"
DB_PASSWORD="${DB_PASSWORD:-ferrero_pass_2025}"
DB_PORT="${DB_PORT:-5433}"
DB_DATA_DIR="${DB_DATA_DIR:-/var/lib/ferrero-tracking/postgres}"
BACKUP_DIR="${BACKUP_DIR:-/var/backups/ferrero-tracking}"

# Check if running as root
if [ "$EUID" -ne 0 ]; then
    echo -e "${RED}❌ This script must be run as root or with sudo${NC}"
    exit 1
fi

echo -e "${BLUE}╔════════════════════════════════════════════════════════════════╗${NC}"
echo -e "${BLUE}║  Ferrero Asset Tracking Database - Production Deployment      ║${NC}"
echo -e "${BLUE}╚════════════════════════════════════════════════════════════════╝${NC}"
echo ""

##############################################################################
# Step 1: Pre-flight Checks
##############################################################################

echo -e "${YELLOW}[1/10] Running pre-flight checks...${NC}"

# Check Docker
if ! command -v docker &> /dev/null; then
    echo -e "${RED}❌ Docker is not installed${NC}"
    echo "Install Docker: https://docs.docker.com/engine/install/"
    exit 1
fi
echo -e "${GREEN}✅ Docker installed: $(docker --version)${NC}"

# Check Docker Compose
if ! docker compose version &> /dev/null; then
    echo -e "${RED}❌ Docker Compose is not installed${NC}"
    echo "Install Docker Compose: https://docs.docker.com/compose/install/"
    exit 1
fi
echo -e "${GREEN}✅ Docker Compose installed: $(docker compose version)${NC}"

# Check if port is available
if netstat -tuln 2>/dev/null | grep -q ":${DB_PORT} " || ss -tuln 2>/dev/null | grep -q ":${DB_PORT} "; then
    echo -e "${RED}❌ Port ${DB_PORT} is already in use${NC}"
    echo "Change DB_PORT in this script or stop the service using that port"
    exit 1
fi
echo -e "${GREEN}✅ Port ${DB_PORT} is available${NC}"

# Check disk space (need at least 10GB)
AVAILABLE_SPACE=$(df -BG . | tail -1 | awk '{print $4}' | sed 's/G//')
if [ "$AVAILABLE_SPACE" -lt 10 ]; then
    echo -e "${YELLOW}⚠ Warning: Less than 10GB disk space available${NC}"
fi

echo ""

##############################################################################
# Step 2: Create Directories
##############################################################################

echo -e "${YELLOW}[2/10] Creating directories...${NC}"

mkdir -p "$DB_DATA_DIR"
mkdir -p "$BACKUP_DIR"
mkdir -p "$(dirname $0)/database"

echo -e "${GREEN}✅ Data directory: $DB_DATA_DIR${NC}"
echo -e "${GREEN}✅ Backup directory: $BACKUP_DIR${NC}"
echo ""

##############################################################################
# Step 3: Create Docker Compose Configuration
##############################################################################

echo -e "${YELLOW}[3/10] Creating Docker Compose configuration...${NC}"

cat > docker-compose.production.yml <<EOF
version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: ferrero-tracking-db-prod
    restart: always
    environment:
      POSTGRES_DB: ${DB_NAME}
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_INITDB_ARGS: "--encoding=UTF8 --auth=md5"
      POSTGRES_HOST_AUTH_METHOD: md5
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "${DB_PORT}:5432"
    volumes:
      - ${DB_DATA_DIR}:/var/lib/postgresql/data
      - ./database/init.sql:/docker-entrypoint-initdb.d/01-init.sql
      - ./database/seed.sql:/docker-entrypoint-initdb.d/02-seed.sql
    networks:
      - ferrero-network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${DB_USER} -d ${DB_NAME}"]
      interval: 10s
      timeout: 5s
      retries: 5
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

networks:
  ferrero-network:
    driver: bridge
EOF

echo -e "${GREEN}✅ Docker Compose configuration created${NC}"
echo ""

##############################################################################
# Step 4: Create Environment Configuration
##############################################################################

echo -e "${YELLOW}[4/10] Creating environment configuration...${NC}"

cat > .env.production <<EOF
# Ferrero Asset Tracking Database - Production Configuration
# Generated: $(date)

# Database Configuration
DB_HOST=localhost
DB_PORT=${DB_PORT}
DB_NAME=${DB_NAME}
DB_USER=${DB_USER}
DB_PASSWORD=${DB_PASSWORD}

# Directories
DB_DATA_DIR=${DB_DATA_DIR}
BACKUP_DIR=${BACKUP_DIR}

# Backup Settings
BACKUP_RETENTION_DAYS=30
BACKUP_SCHEDULE="0 2 * * *"  # 2 AM daily

# Security
ALLOWED_IPS="0.0.0.0/0"  # Change this in production!
EOF

chmod 600 .env.production
echo -e "${GREEN}✅ Environment configuration created: .env.production${NC}"
echo ""

##############################################################################
# Step 5: Deploy Database
##############################################################################

echo -e "${YELLOW}[5/10] Deploying PostgreSQL database...${NC}"

docker compose -f docker-compose.production.yml up -d

echo ""
echo "Waiting for database to be ready..."
sleep 10

# Wait for healthy status
RETRY_COUNT=0
MAX_RETRIES=30
while [ $RETRY_COUNT -lt $MAX_RETRIES ]; do
    if docker compose -f docker-compose.production.yml ps | grep -q "healthy"; then
        echo -e "${GREEN}✅ Database is healthy and ready${NC}"
        break
    fi
    echo -n "."
    sleep 2
    RETRY_COUNT=$((RETRY_COUNT + 1))
done

if [ $RETRY_COUNT -eq $MAX_RETRIES ]; then
    echo -e "${RED}❌ Database failed to become healthy${NC}"
    docker compose -f docker-compose.production.yml logs postgres
    exit 1
fi

echo ""

##############################################################################
# Step 6: Verify Database Installation
##############################################################################

echo -e "${YELLOW}[6/10] Verifying database installation...${NC}"

# Test connection
PGPASSWORD=$DB_PASSWORD psql -h localhost -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT 1;" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo -e "${GREEN}✅ Database connection successful${NC}"
else
    echo -e "${RED}❌ Database connection failed${NC}"
    exit 1
fi

# Check tables
TABLE_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h localhost -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" | tr -d ' ')
echo -e "${GREEN}✅ Tables created: ${TABLE_COUNT}${NC}"

# Check seed data
ASSET_COUNT=$(PGPASSWORD=$DB_PASSWORD psql -h localhost -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT COUNT(*) FROM master_assets;" | tr -d ' ')
echo -e "${GREEN}✅ Sample assets loaded: ${ASSET_COUNT}${NC}"

echo ""

##############################################################################
# Step 7: Configure Firewall (if available)
##############################################################################

echo -e "${YELLOW}[7/10] Configuring firewall...${NC}"

# UFW (Ubuntu/Debian)
if command -v ufw &> /dev/null; then
    echo "Detected UFW firewall"
    ufw allow $DB_PORT/tcp comment 'Ferrero Tracking Database'
    echo -e "${GREEN}✅ UFW rule added for port ${DB_PORT}${NC}"
# firewalld (CentOS/RHEL)
elif command -v firewall-cmd &> /dev/null; then
    echo "Detected firewalld"
    firewall-cmd --permanent --add-port=${DB_PORT}/tcp
    firewall-cmd --reload
    echo -e "${GREEN}✅ Firewalld rule added for port ${DB_PORT}${NC}"
else
    echo -e "${YELLOW}⚠ No firewall detected. Manually configure if needed.${NC}"
fi

echo ""

##############################################################################
# Step 8: Create Backup Script
##############################################################################

echo -e "${YELLOW}[8/10] Creating backup script...${NC}"

cat > /usr/local/bin/ferrero-db-backup.sh <<'BACKUP_SCRIPT'
#!/bin/bash
# Ferrero Database Backup Script

source $(dirname $0)/.env.production 2>/dev/null || true

BACKUP_FILE="${BACKUP_DIR}/ferrero_tracking_$(date +%Y%m%d_%H%M%S).sql"
BACKUP_RETENTION_DAYS=${BACKUP_RETENTION_DAYS:-30}

echo "Starting backup: $BACKUP_FILE"

PGPASSWORD=$DB_PASSWORD pg_dump -h localhost -p $DB_PORT -U $DB_USER $DB_NAME > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    gzip "$BACKUP_FILE"
    echo "✅ Backup successful: ${BACKUP_FILE}.gz"

    # Clean old backups
    find "$BACKUP_DIR" -name "ferrero_tracking_*.sql.gz" -mtime +$BACKUP_RETENTION_DAYS -delete
    echo "✅ Old backups cleaned (retention: ${BACKUP_RETENTION_DAYS} days)"
else
    echo "❌ Backup failed"
    exit 1
fi
BACKUP_SCRIPT

chmod +x /usr/local/bin/ferrero-db-backup.sh
cp .env.production /usr/local/bin/.env.production

echo -e "${GREEN}✅ Backup script created: /usr/local/bin/ferrero-db-backup.sh${NC}"

# Create initial backup
echo "Creating initial backup..."
/usr/local/bin/ferrero-db-backup.sh

echo ""

##############################################################################
# Step 9: Set Up Cron Job for Backups
##############################################################################

echo -e "${YELLOW}[9/10] Setting up automated backups...${NC}"

# Add cron job for daily backups at 2 AM
CRON_JOB="0 2 * * * /usr/local/bin/ferrero-db-backup.sh >> /var/log/ferrero-db-backup.log 2>&1"

# Check if cron job already exists
if crontab -l 2>/dev/null | grep -q "ferrero-db-backup.sh"; then
    echo -e "${YELLOW}⚠ Cron job already exists${NC}"
else
    (crontab -l 2>/dev/null; echo "$CRON_JOB") | crontab -
    echo -e "${GREEN}✅ Daily backup scheduled (2:00 AM)${NC}"
fi

echo ""

##############################################################################
# Step 10: Create Management Scripts
##############################################################################

echo -e "${YELLOW}[10/10] Creating management scripts...${NC}"

# Database status script
cat > /usr/local/bin/ferrero-db-status.sh <<'EOF'
#!/bin/bash
source $(dirname $0)/.env.production 2>/dev/null || true

echo "Ferrero Tracking Database Status"
echo "================================="
echo ""
echo "Container Status:"
docker ps --filter name=ferrero-tracking-db-prod --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}"
echo ""
echo "Database Statistics:"
PGPASSWORD=$DB_PASSWORD psql -h localhost -p $DB_PORT -U $DB_USER -d $DB_NAME -c "
SELECT
    'Master Assets' as category, COUNT(*) as count FROM master_assets WHERE is_deleted = FALSE
UNION ALL
SELECT 'Derivative Assets', COUNT(*) FROM derivative_assets
UNION ALL
SELECT 'Tracking IDs', COUNT(*) FROM tracking_id_log
UNION ALL
SELECT 'Lifecycle Events', COUNT(*) FROM asset_lifecycle_events
ORDER BY category;
"
EOF

chmod +x /usr/local/bin/ferrero-db-status.sh

# Database restart script
cat > /usr/local/bin/ferrero-db-restart.sh <<EOF
#!/bin/bash
cd $(pwd)
docker compose -f docker-compose.production.yml restart postgres
echo "✅ Database restarted"
EOF

chmod +x /usr/local/bin/ferrero-db-restart.sh

echo -e "${GREEN}✅ Management scripts created:${NC}"
echo "   - /usr/local/bin/ferrero-db-status.sh"
echo "   - /usr/local/bin/ferrero-db-restart.sh"
echo "   - /usr/local/bin/ferrero-db-backup.sh"
echo ""

##############################################################################
# Deployment Complete
##############################################################################

echo -e "${GREEN}╔════════════════════════════════════════════════════════════════╗${NC}"
echo -e "${GREEN}║  ✅ Deployment Complete!                                       ║${NC}"
echo -e "${GREEN}╚════════════════════════════════════════════════════════════════╝${NC}"
echo ""
echo -e "${BLUE}Database Information:${NC}"
echo "  Host: localhost (or $(hostname -I | awk '{print $1}'))"
echo "  Port: ${DB_PORT}"
echo "  Database: ${DB_NAME}"
echo "  User: ${DB_USER}"
echo "  Password: ${DB_PASSWORD}"
echo ""
echo -e "${BLUE}Data Locations:${NC}"
echo "  PostgreSQL Data: ${DB_DATA_DIR}"
echo "  Backups: ${BACKUP_DIR}"
echo ""
echo -e "${BLUE}Management Commands:${NC}"
echo "  Status:  ferrero-db-status.sh"
echo "  Backup:  ferrero-db-backup.sh"
echo "  Restart: ferrero-db-restart.sh"
echo "  Logs:    docker compose -f docker-compose.production.yml logs -f postgres"
echo ""
echo -e "${YELLOW}⚠ IMPORTANT - Security Next Steps:${NC}"
echo "  1. Change the database password in .env.production"
echo "  2. Update docker-compose.production.yml with new password"
echo "  3. Restrict network access (configure ALLOWED_IPS in .env.production)"
echo "  4. Set up SSL/TLS for PostgreSQL (recommended for production)"
echo "  5. Review and apply PostgreSQL security best practices"
echo ""
echo -e "${BLUE}Connection String for Applications:${NC}"
echo "  postgresql://${DB_USER}:${DB_PASSWORD}@localhost:${DB_PORT}/${DB_NAME}"
echo ""
echo -e "${BLUE}Test Connection:${NC}"
echo "  PGPASSWORD=${DB_PASSWORD} psql -h localhost -p ${DB_PORT} -U ${DB_USER} -d ${DB_NAME}"
echo ""
echo -e "${GREEN}Deployment log saved to: /var/log/ferrero-db-deployment.log${NC}"

# Save deployment info
cat > /var/log/ferrero-db-deployment.log <<LOGEOF
Ferrero Asset Tracking Database Deployment
==========================================
Date: $(date)
Hostname: $(hostname)
User: $(whoami)

Configuration:
  DB_NAME: ${DB_NAME}
  DB_USER: ${DB_USER}
  DB_PORT: ${DB_PORT}
  DB_DATA_DIR: ${DB_DATA_DIR}
  BACKUP_DIR: ${BACKUP_DIR}

Docker Version: $(docker --version)
Docker Compose Version: $(docker compose version)

Status: DEPLOYED SUCCESSFULLY
LOGEOF

echo -e "${GREEN}✅ Deployment complete!${NC}"