# =============================================================================
# SECURITY NOTICE: This script preview has passwords redacted for security.
#
# When this script is downloaded and executed via:
#   curl -sSL "script_url" | bash
#
# The actual passwords will be included automatically.
# This redaction only applies to browser viewing for security purposes.
# =============================================================================

#!/bin/bash

# Postgresql Deployment Script
# Generated by DeployDB
#
# Database Type: Postgresql
# Database: Sianoimi
# PostgreSQL Version: 17

set -e

# Check if running as root
if [ "$EUID" -ne 0 ]; then
  echo "❌ This script must be run as root!"
  echo ""
  echo "Please run one of the following:"
  echo "  1. sudo su -                    # Switch to root user"
  echo "  2. sudo bash                    # Run bash as root"
  echo ""
  echo "Then run the deployment command again."
  LAST_ERROR="Script must be run as root (EUID=$EUID)"
  exit 1
fi

# Deployment configuration
DEPLOYMENT_ID="eyJfcmFpbHMiOnsiZGF0YSI6MTg4LCJwdXIiOiJwb3N0Z3Jlc3FsX2RlcGxveW1lbnQifX0--5390ad0e0288875a6561691159a24a0c64fb921dd61383b66500c80e70edd6e9"
TOKEN_FILE="$HOME/.deploydb/deployment_token"
LOG_URL="https://localhost:3000/api/deployments/logs/${DEPLOYMENT_ID}"
LOG_FILE="/var/log/deploydb_install.log"



# Cluster/Node configuration


# Read deployment token from file with security validation
if [ ! -f "$TOKEN_FILE" ]; then
  echo "❌ Deployment token file not found: $TOKEN_FILE"
  echo ""
  echo "Please ensure you have run the complete deployment command:"
  echo "  mkdir -p ~/.deploydb && chmod 700 ~/.deploydb"
  echo "  echo "YOUR_TOKEN" > ~/.deploydb/deployment_token && chmod 600 ~/.deploydb/deployment_token"
  echo "  curl -sSL "SCRIPT_URL" | bash"
  echo ""
  LAST_ERROR="Deployment token file not found: $TOKEN_FILE"
  exit 1
fi

# Verify file permissions for security
TOKEN_DIR=$(dirname "$TOKEN_FILE")
if [ -d "$TOKEN_DIR" ]; then
  TOKEN_DIR_PERMS=$(stat -c "%a" "$TOKEN_DIR" 2>/dev/null || stat -f "%Lp" "$TOKEN_DIR" 2>/dev/null || echo "unknown")
  if [ "$TOKEN_DIR_PERMS" != "700" ] && [ "$TOKEN_DIR_PERMS" != "unknown" ]; then
    echo "⚠️  Warning: Token directory has weak permissions ($TOKEN_DIR_PERMS). Recommended: 700"
  fi
fi

TOKEN_FILE_PERMS=$(stat -c "%a" "$TOKEN_FILE" 2>/dev/null || stat -f "%Lp" "$TOKEN_FILE" 2>/dev/null || echo "unknown")
if [ "$TOKEN_FILE_PERMS" != "600" ] && [ "$TOKEN_FILE_PERMS" != "unknown" ]; then
  echo "⚠️  Warning: Token file has weak permissions ($TOKEN_FILE_PERMS). Recommended: 600"
fi

# Read token with validation
DEPLOYMENT_TOKEN=[REDACTED] "$TOKEN_FILE" 2>/dev/null | tr -d '\n\r' | head -c 100)
if [ -z "$DEPLOYMENT_TOKEN" ]; then
  echo "❌ Deployment token file is empty or unreadable: $TOKEN_FILE"
  echo ""
  echo "Please ensure you have run the complete deployment command:"
  echo "  mkdir -p ~/.deploydb && chmod 700 ~/.deploydb"
  echo "  echo "YOUR_TOKEN" > ~/.deploydb/deployment_token && chmod 600 ~/.deploydb/deployment_token"
  echo "  curl -sSL "SCRIPT_URL" | bash"
  echo ""
  LAST_ERROR="Deployment token file is empty or unreadable: $TOKEN_FILE"
  exit 1
fi

# Ensure log directory exists and is writable (must happen before any logging)
mkdir -p "$(dirname "$LOG_FILE")" || {
  echo "❌ Cannot create log directory: $(dirname "$LOG_FILE")"
  echo "Please ensure you have root privileges and try again."
  LAST_ERROR="Cannot create log directory: $(dirname "$LOG_FILE")"
  exit 1
}

touch "$LOG_FILE" || {
  echo "❌ Cannot create log file: $LOG_FILE"
  echo "Please ensure you have root privileges and try again."
  LAST_ERROR="Cannot create log file: $LOG_FILE"
  exit 1
}

# Function to send logs to server
send_log() {
  local level="$1"
  local message="$2"
  
  # Prepare log data with cluster information
  local log_data="{\"log\": {\"message\": \"$message\", \"level\": \"$level\"}}"
  
  # Add cluster-specific data if this is a cluster deployment
  if [ "$CLUSTER_DEPLOYMENT" = "true" ]; then
    log_data="{\"log\": {\"message\": \"$message\", \"level\": \"$level\"}, \"node_type\": \"$NODE_TYPE\"}"
    
    # Add replica number for replica nodes
    if [ "$NODE_TYPE" = "replica" ] && [ -n "$REPLICA_NUMBER" ]; then
      log_data="{\"log\": {\"message\": \"$message\", \"level\": \"$level\"}, \"node_type\": \"$NODE_TYPE\", \"replica_number\": $REPLICA_NUMBER}"
    fi
  fi
  
  curl -s -X POST "$LOG_URL" \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer $DEPLOYMENT_TOKEN" \
    -d "$log_data" \
    > /dev/null 2>&1 || true
}

# Completion signal function
send_completion_signal() {
  local status="$1"
  local error_message="$2"
  local completion_url="https://localhost:3000/api/deployments/complete/${DEPLOYMENT_ID}"

  log_message "info" "Sending completion signal with status: $status"

  if [ "$status" = "success" ]; then
    curl -s -X POST "$completion_url" \
      -H "Content-Type: application/json" \
      -H "Authorization: Bearer $DEPLOYMENT_TOKEN" \
      -d "{\"status\": \"success\"}" \
      > /dev/null 2>&1 || log_message "warn" "Failed to send completion signal"
  else
    curl -s -X POST "$completion_url" \
      -H "Content-Type: application/json" \
      -H "Authorization: Bearer $DEPLOYMENT_TOKEN" \
      -d "{\"status\": \"failed\", \"error\": \"$error_message\"}" \
      > /dev/null 2>&1 || log_message "warn" "Failed to send completion signal"
  fi
}

# Trap to ensure completion signal is sent on script exit
SCRIPT_SUCCESS=false
LAST_ERROR=""

cleanup_and_signal() {
  local exit_code=$?

  # Clean up deployment token file for security
  if [ -f "$TOKEN_FILE" ]; then
    rm -f "$TOKEN_FILE" 2>/dev/null || true
    echo "🔒 Cleaned up deployment token file for security"
  fi

  # Return to home directory
  cd "$HOME" 2>/dev/null || true

  if [ $exit_code -eq 0 ] && [ "$SCRIPT_SUCCESS" = "true" ]; then
    send_completion_signal "success"
  else
    local error_msg="Script failed with exit code: $exit_code"
    if [ -n "$LAST_ERROR" ]; then
      error_msg="$LAST_ERROR"
    fi
    send_completion_signal "failed" "$error_msg"
  fi
}

trap cleanup_and_signal EXIT

# Override error_exit to capture error message before exiting
error_exit() {
  LAST_ERROR="$1"
  log_message "error" "$1"
  exit 1
}

# Logging function for explicit log calls
log_message() {
  local level="$1"
  local message="$2"
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] [$level] $message" | tee -a "$LOG_FILE"
  send_log "$level" "$message"
}

# Enhanced tee that also sends output to server
smart_tee() {
  while IFS= read -r line; do
    echo "$line" | tee -a "$LOG_FILE"
    # Send non-empty lines to server as info logs
    if [[ -n "$line" && "$line" != *"\r"* ]]; then
      # Clean ANSI escape codes
      clean_line=$(echo "$line" | sed 's/\x1b\[[0-9;]*m//g' | sed 's/\r//g')
      if [[ -n "$clean_line" ]]; then
        send_log "info" "$clean_line"
      fi
    fi
  done
}

# Send initial log to indicate script has started
if [ "$CLUSTER_DEPLOYMENT" = "true" ]; then
  send_log "info" "Deployment script initialized - starting PostgreSQL $NODE_TYPE node installation..."
else
  send_log "info" "Deployment script initialized - starting PostgreSQL installation..."
fi

# Redirect all output through our smart tee
exec > >(smart_tee) 2>&1

if [ "$CLUSTER_DEPLOYMENT" = "true" ]; then
  log_message "info" "Starting PostgreSQL $NODE_TYPE node deployment..."
else
  log_message "info" "Starting PostgreSQL deployment..."
fi

# Auto-detect server information
log_message "info" "Detecting server information..."
SERVER_HOSTNAME=$(hostname -f 2>/dev/null || hostname)
SERVER_IP=$(hostname -I 2>/dev/null | awk '{print $1}' || echo "N/A")
SSH_USER=$(whoami)

log_message "info" "Server Information:"
log_message "info" "  Hostname: $SERVER_HOSTNAME"
log_message "info" "  IP Address: $SERVER_IP"
log_message "info" "  Current User: $SSH_USER"
log_message "info" "  Database Type: Postgresql"
log_message "info" "  Database Name: Sianoimi"
log_message "info" "  Version: 17"

# Add cluster information if this is a cluster deployment
if [ "$CLUSTER_DEPLOYMENT" = "true" ]; then
  log_message "info" "  Cluster Deployment: Yes"
  log_message "info" "  Node Type: $NODE_TYPE"
  log_message "info" "  Replica Count: $REPLICA_COUNT"
  
  if [ "$NODE_TYPE" = "replica" ] && [ -n "$REPLICA_NUMBER" ]; then
    log_message "info" "  Replica Number: $REPLICA_NUMBER"
  fi
else
  log_message "info" "  Cluster Deployment: No"
fi

# Check system requirements
log_message "info" "Checking system requirements for PostgreSQL..."

# Check if running as root
if [ "$EUID" -ne 0 ]; then
  log_message "error" "This script must be run as root"
  exit 1
fi

# Check Ubuntu version
if ! grep -q "Ubuntu" /etc/os-release; then
  log_message "error" "This script is designed for Ubuntu systems"
  exit 1
fi

# Check internet connectivity
if ! ping -c 1 google.com > /dev/null 2>&1; then
  log_message "warn" "Internet connectivity check failed - installation may fail"
fi

# Check if PostgreSQL is already installed
log_message "info" "Checking for existing PostgreSQL installation..."

# Check for PostgreSQL binaries
if command -v psql >/dev/null 2>&1 || command -v postgres >/dev/null 2>&1; then
  log_message "error" "PostgreSQL appears to be already installed on this system"
  log_message "error" "Found PostgreSQL binaries:"
  
  if command -v psql >/dev/null 2>&1; then
    PSQL_VERSION=$(psql --version 2>/dev/null || echo "Version unavailable")
    log_message "error" "  psql: $PSQL_VERSION"
  fi
  
  if command -v postgres >/dev/null 2>&1; then
    POSTGRES_VERSION=$(postgres --version 2>/dev/null || echo "Version unavailable")
    log_message "error" "  postgres: $POSTGRES_VERSION"
  fi
  
  log_message "error" ""
  log_message "error" "This deployment script is designed for fresh systems without PostgreSQL."
  log_message "error" "Installing over an existing PostgreSQL installation could:"
  log_message "error" "  - Overwrite existing databases and configurations"
  log_message "error" "  - Create version conflicts"
  log_message "error" "  - Cause data loss"
  log_message "error" ""
  log_message "error" "If you want to proceed anyway, please:"
  log_message "error" "  1. Backup all existing PostgreSQL data"
  log_message "error" "  2. Manually remove the existing installation"
  log_message "error" "  3. Then run this deployment script again"
  log_message "error" ""
  log_message "error" "Deployment aborted for safety."
  LAST_ERROR="PostgreSQL already installed - deployment aborted for safety"
  exit 1
fi

# Check for PostgreSQL packages
if dpkg -l | grep -q postgresql; then
  log_message "error" "PostgreSQL packages detected:"
  dpkg -l | grep postgresql | while read line; do
    log_message "error" "  $line"
  done
  log_message "error" ""
  log_message "error" "Please remove existing PostgreSQL packages before running this deployment."
  log_message "error" "You can remove them with: apt-get remove --purge postgresql*"
  log_message "error" ""
  log_message "error" "Deployment aborted for safety."
  LAST_ERROR="PostgreSQL packages already installed - deployment aborted for safety"
  exit 1
fi

# Check for PostgreSQL service
if systemctl list-units --full -all | grep -Fq "postgresql.service"; then
  log_message "error" "PostgreSQL service detected in systemd"
  log_message "error" "Service status:"
  systemctl status postgresql.service --no-pager -l 2>/dev/null || echo "Service status unavailable"
  log_message "error" ""
  log_message "error" "Please remove existing PostgreSQL installation before proceeding."
  log_message "error" "Deployment aborted for safety."
  LAST_ERROR="PostgreSQL service detected - deployment aborted for safety"
  exit 1
fi

# Check for PostgreSQL data directories
if [ -d "/var/lib/postgresql" ] && [ "$(ls -A /var/lib/postgresql 2>/dev/null)" ]; then
  log_message "error" "PostgreSQL data directory found: /var/lib/postgresql"
  log_message "error" "Contents:"
  ls -la /var/lib/postgresql/ 2>/dev/null | head -10
  log_message "error" ""
  log_message "error" "This suggests a previous PostgreSQL installation."
  log_message "error" "Please backup and remove existing data before proceeding."
  log_message "error" "Deployment aborted for safety."
  LAST_ERROR="PostgreSQL data directory exists - deployment aborted for safety"
  exit 1
fi

# Check for PostgreSQL configuration directories
if [ -d "/etc/postgresql" ] && [ "$(ls -A /etc/postgresql 2>/dev/null)" ]; then
  log_message "error" "PostgreSQL configuration directory found: /etc/postgresql"
  log_message "error" "Contents:"
  ls -la /etc/postgresql/ 2>/dev/null
  log_message "error" ""
  log_message "error" "This suggests a previous PostgreSQL installation."
  log_message "error" "Please backup and remove existing configuration before proceeding."
  log_message "error" "Deployment aborted for safety."
  LAST_ERROR="PostgreSQL configuration directory exists - deployment aborted for safety"
  exit 1
fi

log_message "info" "✅ No existing PostgreSQL installation detected"
log_message "info" "System requirements check completed successfully"


# Prepare PostgreSQL installation
log_message "info" "Preparing PostgreSQL 17 installation..."

# Update package list
log_message "info" "Updating package list..."
apt-get update -qq || {
  log_message "error" "Failed to update package list"
  exit 1
}

# Install common required packages
log_message "info" "Installing required system packages..."
apt-get install -y wget ca-certificates curl gnupg lsb-release || {
  log_message "error" "Failed to install required packages"
  exit 1
}

log_message "info" "Package list updated and required packages installed successfully"


# Add PostgreSQL official APT repository
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - || {
  log_message "error" "Failed to add PostgreSQL GPG key"
  exit 1
}
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

# Update package list again after adding repository
apt-get update -qq || {
  log_message "error" "Failed to update package list after adding PostgreSQL repository"
  exit 1
}

log_message "info" "PostgreSQL repository added successfully"

# Install PostgreSQL 17
log_message "info" "Installing PostgreSQL 17..."
log_message "info" "Downloading and installing PostgreSQL packages (this may take a few minutes)..."

# Install PostgreSQL packages
DEBIAN_FRONTEND=noninteractive apt-get install -y \
  postgresql-17 \
  postgresql-client-17 \
  postgresql-contrib-17 || {
  log_message "error" "Failed to install PostgreSQL"
  exit 1
}

log_message "info" "PostgreSQL 17 installed successfully"

# Start PostgreSQL service (with fallback for containers without systemd)
if command -v systemctl >/dev/null 2>&1 && systemctl is-system-running >/dev/null 2>&1; then
  systemctl start postgresql || {
    log_message "warn" "systemctl failed, trying manual startup..."
    sudo -u postgres /usr/lib/postgresql/17/bin/pg_ctl start -D /var/lib/postgresql/17/main -l /var/log/postgresql/postgresql-17-main.log -o "-c config_file=/etc/postgresql/17/main/postgresql.conf"
    if [ $? -eq 0 ]; then
      log_message "info" "PostgreSQL started manually after systemctl failure"
    else
      log_message "error" "Failed to start PostgreSQL service and manual startup failed"
      exit 1
    fi
  }
  systemctl enable postgresql || {
    log_message "warn" "Failed to enable PostgreSQL service - continuing anyway"
  }
  log_message "info" "PostgreSQL service started and enabled"
else
  log_message "info" "systemctl not available, starting PostgreSQL manually..."
  # Create log directory for manual startup
  mkdir -p /var/log/postgresql
  chown postgres:postgres /var/log/postgresql
  
  # Start PostgreSQL manually (for containers without systemd)
  sudo -u postgres /usr/lib/postgresql/17/bin/pg_ctl start -D /var/lib/postgresql/17/main -l /var/log/postgresql/postgresql-17-main.log -o "-c config_file=/etc/postgresql/17/main/postgresql.conf"
  
  if [ $? -eq 0 ]; then
    log_message "info" "PostgreSQL started manually"
  else
    log_message "error" "Failed to start PostgreSQL manually"
    log_message "info" "PostgreSQL log contents:"
    cat /var/log/postgresql/postgresql-17-main.log 2>/dev/null || echo "No log file found"
    exit 1
  fi
fi

        # Configure PostgreSQL database and user
        log_message "info" "Configuring database 'Sianoimi' and user 'Sianoimi'..."
        
        # Use configured database password
DB_PASSWORD="[REDACTED]"
log_message "info" "Using configured password for database user"

        
        # Create database and user (with error handling for existing objects)
        
        # Create or update user
        if sudo -u postgres psql << EOF
CREATE USER Sianoimi WITH PASSWORD '[REDACTED]';
\q
EOF
        then
          echo "Created new user Sianoimi"
        else
          echo "User creation failed, trying to update existing user..."
          sudo -u postgres psql << EOF
ALTER USER Sianoimi WITH PASSWORD '[REDACTED]';
\q
EOF
          echo "Updated password for existing user Sianoimi"
        fi
        
        # Create database if it doesn't exist
        if sudo -u postgres psql << EOF
CREATE DATABASE Sianoimi OWNER Sianoimi;
\q
EOF
        then
          echo "Created database Sianoimi"
        else
          echo "Database Sianoimi may already exist, continuing..."
        fi
        
        # Grant privileges and set permissions (these are idempotent)
        sudo -u postgres psql << EOF
GRANT ALL PRIVILEGES ON DATABASE Sianoimi TO Sianoimi;
ALTER USER Sianoimi CREATEDB;
\q
EOF
        
        if [ $? -eq 0 ]; then
          log_message "info" "Database and user configuration completed successfully"
          log_message "info" "Database: Sianoimi"
          log_message "info" "User: Sianoimi"
          log_message "info" "Password: $DB_PASSWORD"
        else
          log_message "error" "Failed to configure database or user"
          log_message "error" "PostgreSQL setup cannot continue due to database configuration errors"
          log_message "error" "Please check PostgreSQL logs for more details"
          exit 1
        fi
        
        # Install essential PostgreSQL extensions
        log_message "info" "Installing PostgreSQL extensions..."
        sudo -u postgres psql -d Sianoimi << 'EOF'
\set ON_ERROR_STOP on
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "citext";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
\q
EOF
        
        if [ $? -eq 0 ]; then
          log_message "info" "Extensions installed successfully"
        else
          log_message "warn" "Some extensions may have failed to install"
        fi
        
                # Configure PostgreSQL server settings
        log_message "info" "Configuring PostgreSQL server settings..."
        
        # Backup original postgresql.conf
        cp /etc/postgresql/17/main/postgresql.conf /etc/postgresql/17/main/postgresql.conf.backup.$(date +%Y%m%d_%H%M%S)
        
        # Update postgresql.conf with security-aware settings
        cat >> /etc/postgresql/17/main/postgresql.conf << EOF

# Security-aware connection settings
listen_addresses = 'localhost'
port = 5432
EOF
        
        log_message "info" "PostgreSQL server settings configured"

        
        # Configure pg_hba.conf for secure authentication
        log_message "info" "Configuring PostgreSQL authentication..."
        
        # Backup original pg_hba.conf
        cp /etc/postgresql/17/main/pg_hba.conf /etc/postgresql/17/main/pg_hba.conf.backup.$(date +%Y%m%d_%H%M%S)
        
        # Create a new pg_hba.conf with security-aware authentication rules
        cat > /etc/postgresql/17/main/pg_hba.conf << EOF
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# Local password authentication for Sianoimi
local   Sianoimi    Sianoimi                    md5
host    Sianoimi    Sianoimi    127.0.0.1/32   md5
host    Sianoimi    Sianoimi    ::1/128         md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
EOF
        
        # Reload PostgreSQL configuration
        if command -v systemctl >/dev/null 2>&1 && systemctl is-system-running >/dev/null 2>&1; then
          systemctl reload postgresql || {
            log_message "warn" "Failed to reload PostgreSQL configuration via systemctl"
            sudo -u postgres /usr/lib/postgresql/17/bin/pg_ctl reload -D /var/lib/postgresql/17/main
          }
        else
          sudo -u postgres /usr/lib/postgresql/17/bin/pg_ctl reload -D /var/lib/postgresql/17/main
        fi
        
        log_message "info" "PostgreSQL configuration completed"

# Test PostgreSQL installation
log_message "info" "Testing PostgreSQL installation..."

# Test PostgreSQL service status
if command -v systemctl >/dev/null 2>&1 && systemctl is-system-running >/dev/null 2>&1; then
  if systemctl is-active --quiet postgresql; then
    log_message "info" "PostgreSQL service is running"
  else
    log_message "error" "PostgreSQL service is not running"
    exit 1
  fi
else
  # Check if PostgreSQL process is running manually
  if pgrep -f "postgres.*main" > /dev/null; then
    log_message "info" "PostgreSQL process is running"
  else
    log_message "error" "PostgreSQL process is not running"
    exit 1
  fi
fi

# Test PostgreSQL connection as postgres user
sudo -u postgres psql -c "SELECT version();" > /dev/null 2>&1
if [ $? -eq 0 ]; then
  log_message "info" "PostgreSQL connection test successful"
else
  log_message "error" "PostgreSQL connection test failed"
  exit 1
fi

# Test database connection with created user (use TCP to match md5 authentication)
log_message "info" "Testing database user connection..."

# Use localhost TCP connection to match the md5 authentication setup
if PGPASSWORD="[REDACTED]" psql -h localhost -U Sianoimi -d Sianoimi -c "SELECT current_database();" > /dev/null 2>&1; then
  log_message "info" "Database user connection test successful (localhost TCP)"
else
  log_message "warn" "Database user connection test failed - checking authentication settings..."
  
  # Debug information
  log_message "info" "Checking pg_hba.conf entries for Sianoimi:"
  grep "Sianoimi" /etc/postgresql/17/main/pg_hba.conf || log_message "info" "No specific entries found for Sianoimi"
  
  log_message "info" "Full pg_hba.conf authentication rules:"
  cat /etc/postgresql/17/main/pg_hba.conf | grep -v "^#" | grep -v "^$"
  
  log_message "info" "Attempting connection with verbose output:"
  PGPASSWORD="[REDACTED]" psql -h localhost -U Sianoimi -d Sianoimi -c "SELECT current_database();" 2>&1 | head -10
  
  log_message "error" "Database user connection test failed"
  log_message "error" "Please verify the pg_hba.conf configuration allows md5 authentication for Sianoimi"
  exit 1
fi

# Display PostgreSQL version
PG_VERSION=$(sudo -u postgres psql -t -c "SELECT version();" | head -n1 | tr -d ' ')
log_message "info" "PostgreSQL Version: $PG_VERSION"

log_message "info" "PostgreSQL installation test completed successfully"


log_message "info" "Postgresql deployment script executed successfully!"
log_message "info" "Database: Sianoimi"
log_message "info" "User: Sianoimi"
log_message "info" "Deployment completed successfully"


# Mark script as successful
SCRIPT_SUCCESS=true

# Send final logs
if [ "$CLUSTER_DEPLOYMENT" = "true" ]; then
  log_message "info" "Postgresql $NODE_TYPE node installation completed successfully!"
  log_message "info" "Database: Sianoimi"
  log_message "info" "User: Sianoimi"
  log_message "info" "Node Type: $NODE_TYPE"
  
  if [ "$NODE_TYPE" = "replica" ] && [ -n "$REPLICA_NUMBER" ]; then
    log_message "info" "Replica Number: $REPLICA_NUMBER"
  fi
  
  log_message "info" "$NODE_TYPE node deployment completed successfully"
else
  log_message "info" "Postgresql installation completed successfully!"
  log_message "info" "Database: Sianoimi"
  log_message "info" "User: Sianoimi"
  log_message "info" "Deployment completed successfully"
fi

log_message "info" "Log file saved to: $LOG_FILE"
log_message "info" "Script Token: pIvh5yKU78KWhmC_w5xwLK8WEayxxUB5H3oDpQNfA54"

# Create SSL certificate renewal instructions
log_message "info" "Creating SSL certificate renewal instructions..."

cat > ~/.deploydb/INSTRUCTIONS.md << 'INSTRUCTIONS_EOF'
# PostgreSQL SSL Certificate Renewal Instructions

## Deployment Information
- **Server**: \$(hostname)
- **Database Name**: Sianoimi
- **Database User**: Sianoimi
- **Database Version**: 17
- **Deployment Type**: Single node

**Connection Details:**
```bash
# Connect to database
psql -h \$(hostname) -U Sianoimi -d Sianoimi
```

**Security Note:** Database password has been securely redacted from all logs and removed from our system for security.

## Overview
Your PostgreSQL deployment uses SSL certificates that expire after 365 days. This document provides instructions for renewing these certificates when they expire.

## Certificate Location
- **Certificate**: `/var/lib/postgresql/data/server.crt`
- **Private Key**: `/var/lib/postgresql/data/server.key`
- **Data Directory**: `/var/lib/postgresql/data`


## When to Renew
- Certificates expire **365 days** after installation
- Check expiration: `sudo openssl x509 -in /var/lib/postgresql/data/server.crt -text -noout | grep \"Not After\"`
- Renew **before** expiration to avoid service interruption

## Renewal Process

### 1. Generate New Certificate
```bash
# Stop PostgreSQL service
sudo systemctl stop postgresql

# Navigate to data directory
cd /var/lib/postgresql/data

# Backup existing certificates
sudo cp server.crt server.crt.backup
sudo cp server.key server.key.backup

# Generate new certificate (365 days)
sudo openssl req -new -x509 -days 365 -nodes -text \
  -out server.crt \
  -keyout server.key \
  -subj \"/C=US/ST=State/L=City/O=Organization/CN=\$(hostname)\"

# Set proper permissions
sudo chown postgres:postgres server.crt server.key
sudo chmod 600 server.key
sudo chmod 644 server.crt
```

### 2. Restart PostgreSQL
```bash
# Start PostgreSQL service
sudo systemctl start postgresql

# Verify service is running
sudo systemctl status postgresql
```

### 3. Verify Certificate
```bash
# Check new certificate expiration
sudo openssl x509 -in /var/lib/postgresql/data/server.crt -text -noout | grep \"Not After\"

# Test database connection
sudo -u postgres psql -c \"SELECT version();\"
```


## Important Notes
- Keep certificates backed up before renewal
- Service will be briefly unavailable during renewal
- New certificates are automatically trusted by PostgreSQL
- No configuration changes needed - just restart service

## Need Help?
If you encounter issues during certificate renewal:
1. Check PostgreSQL logs: `sudo journalctl -u postgresql`
2. Verify file permissions on certificates
3. Ensure postgres user owns certificate files
4. Restore backup certificates if needed

---
*Generated by DeployDB - Keep this file for future reference*

INSTRUCTIONS_EOF

chmod 644 ~/.deploydb/INSTRUCTIONS.md
log_message "info" "SSL renewal instructions saved to: ~/.deploydb/INSTRUCTIONS.md"