pg-api 0.2.0

#!/usr/bin/env python3
"""
PostgreSQL API Service Setup Script
Automated installation and configuration tool for pg-api
"""

import os
import sys
import json
import subprocess
import shutil
import argparse
import socket
import hashlib
import platform
import pwd
import grp
import time
import re
import secrets
import logging
from pathlib import Path
from datetime import datetime
from typing import Dict, Optional, List, Tuple
from enum import Enum

# Setup version
SETUP_VERSION = "1.0.0"

# Default configuration
DEFAULT_INSTALL_PATH = "/opt/prod/pg-api"
DEFAULT_SERVICE_USER = "pg-api"
DEFAULT_SERVICE_GROUP = "pg-api"
DEFAULT_APP_PORT = 8580
DEFAULT_LOG_LEVEL = "info"

# Logging configuration
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


class Colors:
    """Terminal colors for output"""
    HEADER = '\033[95m'
    BLUE = '\033[94m'
    CYAN = '\033[96m'
    GREEN = '\033[92m'
    WARNING = '\033[93m'
    FAIL = '\033[91m'
    ENDC = '\033[0m'
    BOLD = '\033[1m'
    UNDERLINE = '\033[4m'


class PostgreSQLVersion:
    """PostgreSQL version management"""
    
    MINIMUM_VERSION = "12.0"
    RECOMMENDED_VERSION = "15.0"
    LATEST_STABLE = "16.0"
    
    @staticmethod
    def parse_version(version_string: str) -> tuple:
        """Parse PostgreSQL version string"""
        match = re.search(r'(\d+)\.(\d+)', version_string)
        if match:
            return (int(match.group(1)), int(match.group(2)))
        return (0, 0)
    
    @staticmethod
    def compare_versions(v1: tuple, v2: tuple) -> int:
        """Compare two version tuples. Returns -1 if v1 < v2, 0 if equal, 1 if v1 > v2"""
        if v1[0] != v2[0]:
            return -1 if v1[0] < v2[0] else 1
        return -1 if v1[1] < v2[1] else (1 if v1[1] > v2[1] else 0)


class PostgreSQLAPISetup:
    """Main setup class for PostgreSQL API Service"""
    
    def __init__(self, args):
        self.args = args
        self.interactive = not args.non_interactive
        self.install_path = args.install_path or DEFAULT_INSTALL_PATH
        self.domain = args.domain
        self.ssl_cert = args.ssl_cert
        self.ssl_key = args.ssl_key
        self.postgres_host = args.postgres_host or "localhost"
        self.postgres_port = args.postgres_port or 5432
        self.opensearch_url = args.opensearch_url
        self.opensearch_token = args.opensearch_token
        self.license_manager_url = args.license_manager_url
        self.license_key = args.license_key
        self.service_user = args.service_user or DEFAULT_SERVICE_USER
        self.service_group = args.service_group or DEFAULT_SERVICE_GROUP
        self.app_port = args.app_port or DEFAULT_APP_PORT
        
        # Runtime variables
        self.postgres_version = None
        self.postgres_installed = False
        self.existing_config = {}
        self.system_info = self._get_system_info()
        
    def _get_system_info(self) -> Dict:
        """Get system information"""
        info = {
            'os': platform.system().lower(),
            'distribution': '',
            'version': platform.version(),
            'architecture': platform.machine(),
            'hostname': socket.gethostname(),
            'python_version': sys.version
        }
        
        # Get Linux distribution info
        if info['os'] == 'linux':
            try:
                import distro
                info['distribution'] = distro.id()
                info['dist_version'] = distro.version()
            except ImportError:
                # Fallback to checking files
                if os.path.exists('/etc/os-release'):
                    with open('/etc/os-release') as f:
                        for line in f:
                            if line.startswith('ID='):
                                info['distribution'] = line.split('=')[1].strip().strip('"')
                            elif line.startswith('VERSION_ID='):
                                info['dist_version'] = line.split('=')[1].strip().strip('"')
        
        return info
    
    def run(self):
        """Main execution flow"""
        try:
            self._print_header()
            
            if self.args.action == 'install':
                self.install()
            elif self.args.action == 'upgrade':
                self.upgrade()
            elif self.args.action == 'uninstall':
                self.uninstall()
            elif self.args.action == 'backup':
                self.backup()
            elif self.args.action == 'restore':
                self.restore()
            elif self.args.action == 'check':
                self.check_system()
            else:
                self.install()  # Default action
                
        except KeyboardInterrupt:
            self._print_error("\nInstallation cancelled by user")
            sys.exit(1)
        except Exception as e:
            self._print_error(f"Setup failed: {str(e)}")
            logger.exception("Setup failed with exception")
            sys.exit(1)
    
    def _print_header(self):
        """Print setup header"""
        print(f"{Colors.HEADER}{'='*60}{Colors.ENDC}")
        print(f"{Colors.BOLD}PostgreSQL API Service Setup v{SETUP_VERSION}{Colors.ENDC}")
        print(f"{Colors.HEADER}{'='*60}{Colors.ENDC}")
        print(f"System: {self.system_info['os']} {self.system_info['distribution']}")
        print(f"Architecture: {self.system_info['architecture']}")
        print(f"{Colors.HEADER}{'='*60}{Colors.ENDC}\n")
    
    def _print_success(self, message: str):
        """Print success message"""
        print(f"{Colors.GREEN}✓ {message}{Colors.ENDC}")
    
    def _print_error(self, message: str):
        """Print error message"""
        print(f"{Colors.FAIL}✗ {message}{Colors.ENDC}")
    
    def _print_warning(self, message: str):
        """Print warning message"""
        print(f"{Colors.WARNING}⚠ {message}{Colors.ENDC}")
    
    def _print_info(self, message: str):
        """Print info message"""
        print(f"{Colors.CYAN}ℹ {message}{Colors.ENDC}")
    
    def _run_command(self, command: List[str], check: bool = True, capture_output: bool = True) -> subprocess.CompletedProcess:
        """Run shell command"""
        logger.debug(f"Running command: {' '.join(command)}")
        try:
            result = subprocess.run(
                command,
                check=check,
                capture_output=capture_output,
                text=True
            )
            return result
        except subprocess.CalledProcessError as e:
            logger.error(f"Command failed: {e.stderr}")
            raise
    
    def _prompt(self, message: str, default: str = None) -> str:
        """Prompt user for input"""
        if not self.interactive:
            return default or ""
        
        prompt_msg = f"{Colors.CYAN}{message}{Colors.ENDC}"
        if default:
            prompt_msg += f" [{default}]"
        prompt_msg += ": "
        
        value = input(prompt_msg).strip()
        return value if value else (default or "")
    
    def _confirm(self, message: str, default: bool = False) -> bool:
        """Prompt user for confirmation"""
        if not self.interactive:
            return default
        
        default_str = "Y/n" if default else "y/N"
        prompt_msg = f"{Colors.CYAN}{message} [{default_str}]: {Colors.ENDC}"
        
        while True:
            value = input(prompt_msg).strip().lower()
            if not value:
                return default
            if value in ['y', 'yes']:
                return True
            if value in ['n', 'no']:
                return False
            print("Please answer 'yes' or 'no'")
    
    def check_system(self):
        """Check system requirements and current status"""
        self._print_info("Checking system requirements...")
        
        # Check if running as root
        if os.geteuid() != 0:
            self._print_warning("Not running as root. Some operations may fail.")
        
        # Check PostgreSQL
        self._check_postgresql()
        
        # Check Rust/Cargo
        self._check_rust()
        
        # Check for existing installation
        if os.path.exists(self.install_path):
            self._print_warning(f"Installation directory exists: {self.install_path}")
            if os.path.exists(f"{self.install_path}/target/release/pg-api"):
                self._print_info("pg-api binary found")
        
        # Check systemd
        if shutil.which('systemctl'):
            self._print_success("SystemD is available")
        else:
            self._print_warning("SystemD not found. Service management will be limited.")
        
        # Check web servers
        if shutil.which('nginx'):
            self._print_success("Nginx is installed")
        elif shutil.which('apache2') or shutil.which('httpd'):
            self._print_success("Apache is installed")
        else:
            self._print_warning("No web server found. Manual proxy configuration required.")
        
        # Check firewall
        if shutil.which('ufw'):
            self._print_info("UFW firewall detected")
        elif shutil.which('firewall-cmd'):
            self._print_info("Firewalld detected")
        
        return True
    
    def _check_postgresql(self) -> bool:
        """Check PostgreSQL installation and version"""
        self._print_info("Checking PostgreSQL...")
        
        # Try different PostgreSQL client locations
        psql_paths = [
            'psql',
            '/usr/bin/psql',
            '/usr/local/bin/psql',
            '/usr/pgsql-16/bin/psql',
            '/usr/pgsql-15/bin/psql',
            '/usr/pgsql-14/bin/psql',
            '/usr/pgsql-13/bin/psql',
            '/usr/pgsql-12/bin/psql',
        ]
        
        psql_cmd = None
        for path in psql_paths:
            if shutil.which(path):
                psql_cmd = path
                break
        
        if not psql_cmd:
            self._print_warning("PostgreSQL client (psql) not found")
            self.postgres_installed = False
            return False
        
        # Get PostgreSQL version
        try:
            result = self._run_command([psql_cmd, '--version'], check=False)
            if result.returncode == 0:
                version_str = result.stdout.strip()
                self._print_success(f"PostgreSQL found: {version_str}")
                
                # Parse version
                version_tuple = PostgreSQLVersion.parse_version(version_str)
                self.postgres_version = version_tuple
                
                # Check version requirements
                min_version = PostgreSQLVersion.parse_version(PostgreSQLVersion.MINIMUM_VERSION)
                if PostgreSQLVersion.compare_versions(version_tuple, min_version) < 0:
                    self._print_warning(f"PostgreSQL version {version_tuple} is below minimum {PostgreSQLVersion.MINIMUM_VERSION}")
                    return False
                
                self.postgres_installed = True
                
                # Try to connect to PostgreSQL
                self._test_postgresql_connection()
                
                return True
        except Exception as e:
            self._print_error(f"Failed to check PostgreSQL version: {e}")
            return False
    
    def _test_postgresql_connection(self) -> bool:
        """Test PostgreSQL connection"""
        try:
            cmd = [
                'psql',
                '-h', self.postgres_host,
                '-p', str(self.postgres_port),
                '-U', 'postgres',
                '-c', 'SELECT version();',
                '-t'
            ]
            
            # Use environment variable for password if available
            env = os.environ.copy()
            if 'PGPASSWORD' in env:
                result = self._run_command(cmd, check=False, capture_output=True)
                if result.returncode == 0:
                    self._print_success(f"Successfully connected to PostgreSQL at {self.postgres_host}:{self.postgres_port}")
                    return True
            
            self._print_warning(f"Could not connect to PostgreSQL at {self.postgres_host}:{self.postgres_port}")
            self._print_info("You may need to configure PostgreSQL connection separately")
            return False
            
        except Exception as e:
            self._print_warning(f"PostgreSQL connection test failed: {e}")
            return False
    
    def _check_rust(self) -> bool:
        """Check Rust and Cargo installation"""
        self._print_info("Checking Rust toolchain...")
        
        if not shutil.which('cargo'):
            self._print_warning("Cargo not found. Rust installation required.")
            if self._confirm("Install Rust now?", default=True):
                self._install_rust()
            return False
        
        # Check Rust version
        result = self._run_command(['rustc', '--version'], check=False)
        if result.returncode == 0:
            self._print_success(f"Rust found: {result.stdout.strip()}")
            return True
        
        return False
    
    def _install_rust(self):
        """Install Rust toolchain"""
        self._print_info("Installing Rust...")
        
        # Download and run rustup
        rustup_cmd = "curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- -y"
        result = subprocess.run(rustup_cmd, shell=True, capture_output=True, text=True)
        
        if result.returncode == 0:
            self._print_success("Rust installed successfully")
            # Source cargo env
            cargo_env = os.path.expanduser("~/.cargo/env")
            if os.path.exists(cargo_env):
                subprocess.run(f"source {cargo_env}", shell=True)
        else:
            self._print_error("Failed to install Rust")
            raise Exception("Rust installation failed")
    
    def install(self):
        """Main installation process"""
        self._print_info("Starting PostgreSQL API Service installation...")
        
        # Check system requirements
        self.check_system()
        
        # PostgreSQL setup
        if not self.postgres_installed:
            if self._confirm("PostgreSQL is not installed. Install it now?", default=True):
                self._install_postgresql()
        elif self.postgres_version:
            latest = PostgreSQLVersion.parse_version(PostgreSQLVersion.LATEST_STABLE)
            if PostgreSQLVersion.compare_versions(self.postgres_version, latest) < 0:
                if self._confirm(f"Upgrade PostgreSQL to version {PostgreSQLVersion.LATEST_STABLE}?", default=False):
                    self._upgrade_postgresql()
        
        # Create installation directory
        self._create_install_directory()
        
        # Build pg-api
        self._build_application()
        
        # Create service user
        self._create_service_user()
        
        # Generate configuration
        self._generate_configuration()
        
        # Setup systemd service
        self._setup_systemd_service()
        
        # Configure web server
        if self.domain:
            self._configure_web_server()
        
        # Configure firewall
        self._configure_firewall()
        
        # Start service
        if self._confirm("Start pg-api service now?", default=True):
            self._start_service()
        
        self._print_success("\nInstallation completed successfully!")
        self._print_installation_summary()
    
    def _install_postgresql(self):
        """Install PostgreSQL"""
        self._print_info("Installing PostgreSQL...")
        
        dist = self.system_info.get('distribution', '').lower()
        
        if dist in ['ubuntu', 'debian']:
            # Add PostgreSQL APT repository
            commands = [
                ['apt-get', 'update'],
                ['apt-get', 'install', '-y', 'wget', 'ca-certificates'],
                ['sh', '-c', 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'],
                ['wget', '--quiet', '-O', '-', 'https://www.postgresql.org/media/keys/ACCC4CF8.asc', '|', 'apt-key', 'add', '-'],
                ['apt-get', 'update'],
                ['apt-get', 'install', '-y', f'postgresql-{PostgreSQLVersion.LATEST_STABLE.split(".")[0]}', 'postgresql-contrib']
            ]
        elif dist in ['centos', 'rhel', 'fedora', 'rocky', 'almalinux']:
            # Add PostgreSQL YUM repository
            version = PostgreSQLVersion.LATEST_STABLE.split(".")[0]
            commands = [
                ['yum', 'install', '-y', f'https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm'],
                ['yum', 'install', '-y', f'postgresql{version}-server', f'postgresql{version}-contrib'],
                [f'/usr/pgsql-{version}/bin/postgresql-{version}-setup', 'initdb']
            ]
        else:
            self._print_error(f"Unsupported distribution: {dist}")
            self._print_info("Please install PostgreSQL manually")
            return
        
        # Execute installation commands
        for cmd in commands:
            if '|' in cmd:
                # Handle piped commands
                subprocess.run(' '.join(cmd), shell=True, check=True)
            else:
                self._run_command(cmd)
        
        # Start and enable PostgreSQL
        self._run_command(['systemctl', 'start', 'postgresql'])
        self._run_command(['systemctl', 'enable', 'postgresql'])
        
        self._print_success("PostgreSQL installed successfully")
        self.postgres_installed = True
    
    def _upgrade_postgresql(self):
        """Upgrade PostgreSQL to latest version"""
        self._print_info("Upgrading PostgreSQL...")
        
        # Backup existing data first
        backup_dir = f"/tmp/pg_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        self._print_info(f"Creating backup at {backup_dir}")
        
        os.makedirs(backup_dir, exist_ok=True)
        
        # Dump all databases
        self._run_command([
            'pg_dumpall',
            '-h', self.postgres_host,
            '-p', str(self.postgres_port),
            '-f', f'{backup_dir}/all_databases.sql'
        ])
        
        self._print_success("Backup completed")
        
        # Perform upgrade based on distribution
        dist = self.system_info.get('distribution', '').lower()
        
        if dist in ['ubuntu', 'debian']:
            # Use pg_upgradecluster for Debian-based systems
            old_version = f"{self.postgres_version[0]}"
            new_version = PostgreSQLVersion.LATEST_STABLE.split(".")[0]
            
            commands = [
                ['apt-get', 'install', '-y', f'postgresql-{new_version}'],
                ['pg_upgradecluster', old_version, 'main', new_version, 'main']
            ]
        else:
            self._print_warning("Automatic upgrade not supported for this distribution")
            self._print_info(f"Backup created at {backup_dir}")
            self._print_info("Please perform manual upgrade and restore from backup")
            return
        
        # Execute upgrade
        for cmd in commands:
            self._run_command(cmd)
        
        self._print_success("PostgreSQL upgraded successfully")
    
    def _create_install_directory(self):
        """Create installation directory structure"""
        self._print_info(f"Creating installation directory: {self.install_path}")
        
        # Create directory structure
        dirs = [
            self.install_path,
            f"{self.install_path}/logs",
            f"{self.install_path}/config",
            f"{self.install_path}/backup",
        ]
        
        for dir_path in dirs:
            os.makedirs(dir_path, exist_ok=True)
        
        # Copy source code if not already there
        if not os.path.exists(f"{self.install_path}/src"):
            current_dir = os.path.dirname(os.path.abspath(__file__))
            
            # Copy necessary files and directories
            items_to_copy = ['src', 'Cargo.toml', 'Cargo.lock', 'config']
            for item in items_to_copy:
                src = os.path.join(current_dir, item)
                dst = os.path.join(self.install_path, item)
                if os.path.exists(src):
                    if os.path.isdir(src):
                        shutil.copytree(src, dst, dirs_exist_ok=True)
                    else:
                        shutil.copy2(src, dst)
        
        self._print_success("Installation directory created")
    
    def _build_application(self):
        """Build pg-api application"""
        self._print_info("Building pg-api application...")
        
        # Change to installation directory
        os.chdir(self.install_path)
        
        # Build release binary
        result = self._run_command(['cargo', 'build', '--release'])
        
        if result.returncode == 0:
            self._print_success("Application built successfully")
        else:
            raise Exception("Failed to build application")
    
    def _create_service_user(self):
        """Create system user for service"""
        self._print_info(f"Creating service user: {self.service_user}")
        
        # Check if user exists
        try:
            pwd.getpwnam(self.service_user)
            self._print_info(f"User {self.service_user} already exists")
        except KeyError:
            # Create user
            self._run_command([
                'useradd',
                '-r',  # System user
                '-s', '/bin/false',  # No shell
                '-d', self.install_path,  # Home directory
                '-c', 'PostgreSQL API Service',
                self.service_user
            ])
            self._print_success(f"User {self.service_user} created")
        
        # Set ownership
        self._run_command(['chown', '-R', f'{self.service_user}:{self.service_group}', self.install_path])
    
    def _generate_configuration(self):
        """Generate application configuration"""
        self._print_info("Generating configuration...")
        
        # Ask for OpenSearch configuration if not provided
        if self.interactive and not self.opensearch_url:
            use_opensearch = self._confirm("Enable OpenSearch observability?", default=False)
            if use_opensearch:
                self.opensearch_url = self._prompt("OpenSearch API URL (e.g., https://os.example.com)", 
                                                  default="https://os.timelapseobras.com.br")
                if not self.opensearch_token:
                    self.opensearch_token = self._prompt("OpenSearch API token", 
                                                        default=f"sk_prod_{self.service_user}_{secrets.token_hex(8)}")
        
        # Generate secrets
        jwt_secret = secrets.token_urlsafe(32)
        api_token = f"sk_live_{secrets.token_hex(16)}"
        
        # Create environment file
        env_config = f"""# PostgreSQL API Service Configuration
# Generated on {datetime.now().isoformat()}

# Application Configuration
APP__ADDR=127.0.0.1:{self.app_port}
APP__LOG_LEVEL={DEFAULT_LOG_LEVEL}
APP__DOMAIN={self.domain or 'localhost'}

# Config directory
CONFIG_DIR=/etc/pg-api

# PostgreSQL Configuration
PG__HOST={self.postgres_host}
PG__PORT={self.postgres_port}
PG__MAX_CONNECTIONS=100
PG__POOL_SIZE=25

# OpenSearch Integration (Optional)
OPENSEARCH_API_URL={self.opensearch_url or ''}
OPENSEARCH_API_TOKEN={self.opensearch_token or api_token}
OPENSEARCH_INDEX_PREFIX=pg-api-{socket.gethostname()}
OPENSEARCH_BATCH_SIZE=100
OPENSEARCH_FLUSH_INTERVAL=5
OPENSEARCH_ENABLED={'true' if self.opensearch_url else 'false'}

# License Configuration
LICENSE_MANAGER_URL={self.license_manager_url or ''}
LICENSE_KEY={self.license_key or ''}

# Security
JWT_SECRET={jwt_secret}
API_TOKENS={api_token}

# Rust Logging
RUST_LOG=pg_api=info
"""
        
        # Create /etc/pg-api directory if not exists
        os.makedirs("/etc/pg-api", exist_ok=True)
        
        # Write environment file to /etc/pg-api
        env_file = "/etc/pg-api/pg-api.env"
        with open(env_file, 'w') as f:
            f.write(env_config)
        
        # Copy config files to /etc/pg-api
        config_files = ['accounts.json', 'server.json']
        for config_file in config_files:
            src = f"{self.install_path}/config/{config_file}"
            dst = f"/etc/pg-api/{config_file}"
            if os.path.exists(src):
                shutil.copy2(src, dst)
                self._run_command(['chown', f'root:{self.service_group}', dst])
                os.chmod(dst, 0o640)
        
        # Set permissions for env file
        os.chmod(env_file, 0o640)
        self._run_command(['chown', f'root:{self.service_group}', env_file])
        
        self._print_success("Configuration generated")
        self._print_info(f"Configuration directory: /etc/pg-api")
        self._print_info(f"API Token: {api_token}")
    
    def _setup_systemd_service(self):
        """Setup SystemD service"""
        self._print_info("Setting up SystemD service...")
        
        service_content = f"""[Unit]
Description=PostgreSQL API Service
After=network.target postgresql.service
Wants=postgresql.service

[Service]
Type=simple
User={self.service_user}
Group={self.service_group}
WorkingDirectory={self.install_path}
EnvironmentFile=/etc/pg-api/pg-api.env
ExecStart={self.install_path}/target/release/pg-api
Restart=always
RestartSec=10

# Security
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths={self.install_path}/logs

# Resource Limits
LimitNOFILE=65536
MemoryMax=2G
CPUQuota=400%

# Logging
StandardOutput=journal
StandardError=journal
SyslogIdentifier=pg-api

[Install]
WantedBy=multi-user.target
"""
        
        # Write service file
        service_file = "/etc/systemd/system/pg-api.service"
        with open(service_file, 'w') as f:
            f.write(service_content)
        
        # Reload systemd
        self._run_command(['systemctl', 'daemon-reload'])
        
        # Enable service
        self._run_command(['systemctl', 'enable', 'pg-api'])
        
        self._print_success("SystemD service configured")
    
    def _configure_web_server(self):
        """Configure web server reverse proxy"""
        if not self.domain:
            return
        
        self._print_info(f"Configuring web server for domain: {self.domain}")
        
        # Detect web server
        if shutil.which('nginx'):
            self._configure_nginx()
        elif shutil.which('apache2') or shutil.which('httpd'):
            self._configure_apache()
        else:
            self._print_warning("No supported web server found")
            self._print_info("Please configure reverse proxy manually")
    
    def _configure_nginx(self):
        """Configure Nginx reverse proxy"""
        self._print_info("Configuring Nginx...")
        
        nginx_config = f"""server {{
    listen 80;
    server_name {self.domain};
    
    location / {{
        proxy_pass http://127.0.0.1:{self.app_port};
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        
        # WebSocket support
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        
        # Timeouts
        proxy_connect_timeout 60s;
        proxy_send_timeout 60s;
        proxy_read_timeout 60s;
    }}
    
    location /health {{
        proxy_pass http://127.0.0.1:{self.app_port}/health;
        access_log off;
    }}
}}
"""
        
        # Determine config path
        config_paths = [
            f"/etc/nginx/sites-available/{self.domain}",
            f"/etc/nginx/conf.d/{self.domain}.conf"
        ]
        
        for config_path in config_paths:
            config_dir = os.path.dirname(config_path)
            if os.path.exists(config_dir):
                with open(config_path, 'w') as f:
                    f.write(nginx_config)
                
                # Enable site if using sites-available
                if 'sites-available' in config_path:
                    link_path = config_path.replace('sites-available', 'sites-enabled')
                    if not os.path.exists(link_path):
                        os.symlink(config_path, link_path)
                
                # Test and reload Nginx
                self._run_command(['nginx', '-t'])
                self._run_command(['systemctl', 'reload', 'nginx'])
                
                self._print_success("Nginx configured successfully")
                
                # Setup SSL if requested
                if self._confirm("Setup SSL with Let's Encrypt?", default=False):
                    self._setup_ssl_certbot()
                
                break
    
    def _configure_apache(self):
        """Configure Apache reverse proxy"""
        self._print_info("Configuring Apache...")
        
        # Enable required modules
        modules = ['proxy', 'proxy_http', 'proxy_wstunnel', 'headers']
        for module in modules:
            self._run_command(['a2enmod', module], check=False)
        
        apache_config = f"""<VirtualHost *:80>
    ServerName {self.domain}
    
    ProxyPreserveHost On
    ProxyPass / http://127.0.0.1:{self.app_port}/
    ProxyPassReverse / http://127.0.0.1:{self.app_port}/
    
    # WebSocket support
    RewriteEngine On
    RewriteCond %{{HTTP:Upgrade}} websocket [NC]
    RewriteCond %{{HTTP:Connection}} upgrade [NC]
    RewriteRule ^/?(.*) "ws://127.0.0.1:{self.app_port}/$1" [P,L]
    
    <Location /health>
        ProxyPass http://127.0.0.1:{self.app_port}/health
        SetEnv no-access-log
    </Location>
</VirtualHost>
"""
        
        # Write config
        config_path = f"/etc/apache2/sites-available/{self.domain}.conf"
        with open(config_path, 'w') as f:
            f.write(apache_config)
        
        # Enable site
        self._run_command(['a2ensite', f'{self.domain}.conf'])
        
        # Reload Apache
        self._run_command(['systemctl', 'reload', 'apache2'])
        
        self._print_success("Apache configured successfully")
    
    def _setup_ssl_certbot(self):
        """Setup SSL with Certbot"""
        self._print_info("Setting up SSL with Let's Encrypt...")
        
        # Install certbot if not present
        if not shutil.which('certbot'):
            dist = self.system_info.get('distribution', '').lower()
            if dist in ['ubuntu', 'debian']:
                self._run_command(['apt-get', 'install', '-y', 'certbot', 'python3-certbot-nginx'])
            elif dist in ['centos', 'rhel', 'fedora']:
                self._run_command(['yum', 'install', '-y', 'certbot', 'python3-certbot-nginx'])
        
        # Get SSL certificate
        email = self._prompt("Enter email for SSL certificate notifications")
        if email:
            self._run_command([
                'certbot', '--nginx',
                '-d', self.domain,
                '--non-interactive',
                '--agree-tos',
                '--email', email
            ])
            self._print_success("SSL certificate obtained successfully")
    
    def _configure_firewall(self):
        """Configure firewall rules"""
        self._print_info("Configuring firewall...")
        
        if shutil.which('ufw'):
            # UFW (Ubuntu/Debian)
            commands = [
                ['ufw', 'allow', '80/tcp'],
                ['ufw', 'allow', '443/tcp'],
                ['ufw', 'allow', '22/tcp'],  # SSH
            ]
            
            for cmd in commands:
                self._run_command(cmd, check=False)
            
            self._print_success("UFW firewall configured")
            
        elif shutil.which('firewall-cmd'):
            # Firewalld (CentOS/RHEL/Fedora)
            commands = [
                ['firewall-cmd', '--permanent', '--add-service=http'],
                ['firewall-cmd', '--permanent', '--add-service=https'],
                ['firewall-cmd', '--reload']
            ]
            
            for cmd in commands:
                self._run_command(cmd, check=False)
            
            self._print_success("Firewalld configured")
        else:
            self._print_info("No supported firewall found")
    
    def _start_service(self):
        """Start pg-api service"""
        self._print_info("Starting pg-api service...")
        
        self._run_command(['systemctl', 'start', 'pg-api'])
        
        # Wait for service to start
        time.sleep(2)
        
        # Check status
        result = self._run_command(['systemctl', 'is-active', 'pg-api'], check=False)
        if result.stdout.strip() == 'active':
            self._print_success("Service started successfully")
        else:
            self._print_error("Service failed to start")
            self._run_command(['journalctl', '-u', 'pg-api', '-n', '20'], check=False)
    
    def _print_installation_summary(self):
        """Print installation summary"""
        print(f"\n{Colors.HEADER}{'='*60}{Colors.ENDC}")
        print(f"{Colors.BOLD}Installation Summary{Colors.ENDC}")
        print(f"{Colors.HEADER}{'='*60}{Colors.ENDC}")
        
        print(f"\nInstallation Path: {self.install_path}")
        print(f"Service User: {self.service_user}")
        print(f"Application Port: {self.app_port}")
        
        if self.domain:
            print(f"Domain: {self.domain}")
            print(f"URL: http{'s' if self.ssl_cert else ''}://{self.domain}")
        
        print(f"\nService Management:")
        print(f"  Start:   systemctl start pg-api")
        print(f"  Stop:    systemctl stop pg-api")
        print(f"  Status:  systemctl status pg-api")
        print(f"  Logs:    journalctl -u pg-api -f")
        
        print(f"\nConfiguration File: {self.install_path}/.env")
        
        if self.opensearch_url:
            print(f"\nOpenSearch Integration: {self.opensearch_url}")
        
        print(f"\n{Colors.GREEN}Setup completed successfully!{Colors.ENDC}")
    
    def upgrade(self):
        """Upgrade existing installation"""
        self._print_info("Starting upgrade process...")
        
        if not os.path.exists(self.install_path):
            self._print_error(f"Installation not found at {self.install_path}")
            return
        
        # Stop service
        self._print_info("Stopping service...")
        self._run_command(['systemctl', 'stop', 'pg-api'], check=False)
        
        # Backup current installation
        backup_dir = f"{self.install_path}/backup/upgrade_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        os.makedirs(backup_dir, exist_ok=True)
        
        # Backup binary and config
        shutil.copy2(f"{self.install_path}/target/release/pg-api", backup_dir)
        shutil.copy2(f"{self.install_path}/.env", backup_dir)
        
        self._print_success(f"Backup created at {backup_dir}")
        
        # Build new version
        self._build_application()
        
        # Start service
        self._print_info("Starting service...")
        self._run_command(['systemctl', 'start', 'pg-api'])
        
        self._print_success("Upgrade completed successfully")
    
    def uninstall(self):
        """Uninstall pg-api"""
        self._print_warning("This will remove pg-api installation")
        
        if not self._confirm("Are you sure you want to uninstall?", default=False):
            return
        
        # Stop and disable service
        self._print_info("Stopping service...")
        self._run_command(['systemctl', 'stop', 'pg-api'], check=False)
        self._run_command(['systemctl', 'disable', 'pg-api'], check=False)
        
        # Remove service file
        service_file = "/etc/systemd/system/pg-api.service"
        if os.path.exists(service_file):
            os.remove(service_file)
            self._run_command(['systemctl', 'daemon-reload'])
        
        # Remove web server config
        if self.domain:
            # Nginx
            for path in [f"/etc/nginx/sites-enabled/{self.domain}", 
                        f"/etc/nginx/sites-available/{self.domain}",
                        f"/etc/nginx/conf.d/{self.domain}.conf"]:
                if os.path.exists(path):
                    os.remove(path)
            
            # Apache
            apache_conf = f"/etc/apache2/sites-available/{self.domain}.conf"
            if os.path.exists(apache_conf):
                self._run_command(['a2dissite', f'{self.domain}.conf'], check=False)
                os.remove(apache_conf)
        
        # Remove installation directory
        if self._confirm(f"Remove installation directory {self.install_path}?", default=False):
            shutil.rmtree(self.install_path)
        
        # Remove user
        if self._confirm(f"Remove user {self.service_user}?", default=False):
            self._run_command(['userdel', self.service_user], check=False)
        
        self._print_success("Uninstallation completed")
    
    def backup(self):
        """Backup configuration and data"""
        self._print_info("Creating backup...")
        
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        backup_dir = f"{self.install_path}/backup/manual_{timestamp}"
        os.makedirs(backup_dir, exist_ok=True)
        
        # Backup items
        items = [
            '.env',
            'config',
            'target/release/pg-api'
        ]
        
        for item in items:
            src = f"{self.install_path}/{item}"
            if os.path.exists(src):
                if os.path.isdir(src):
                    shutil.copytree(src, f"{backup_dir}/{item}")
                else:
                    shutil.copy2(src, backup_dir)
        
        # Create tar archive
        archive_path = f"{self.install_path}/backup/pg-api_backup_{timestamp}.tar.gz"
        self._run_command(['tar', '-czf', archive_path, '-C', backup_dir, '.'])
        
        # Cleanup temp dir
        shutil.rmtree(backup_dir)
        
        self._print_success(f"Backup created: {archive_path}")
    
    def restore(self):
        """Restore from backup"""
        backup_dir = f"{self.install_path}/backup"
        
        if not os.path.exists(backup_dir):
            self._print_error("No backups found")
            return
        
        # List available backups
        backups = [f for f in os.listdir(backup_dir) if f.endswith('.tar.gz')]
        
        if not backups:
            self._print_error("No backup archives found")
            return
        
        print("\nAvailable backups:")
        for i, backup in enumerate(backups, 1):
            print(f"  {i}. {backup}")
        
        choice = self._prompt("Select backup to restore (number)")
        
        try:
            backup_file = backups[int(choice) - 1]
        except (ValueError, IndexError):
            self._print_error("Invalid selection")
            return
        
        # Stop service
        self._print_info("Stopping service...")
        self._run_command(['systemctl', 'stop', 'pg-api'], check=False)
        
        # Extract backup
        temp_dir = f"/tmp/pg-api_restore_{int(time.time())}"
        os.makedirs(temp_dir)
        
        self._run_command(['tar', '-xzf', f"{backup_dir}/{backup_file}", '-C', temp_dir])
        
        # Restore files
        for item in os.listdir(temp_dir):
            src = f"{temp_dir}/{item}"
            dst = f"{self.install_path}/{item}"
            
            if os.path.exists(dst):
                if os.path.isdir(dst):
                    shutil.rmtree(dst)
                else:
                    os.remove(dst)
            
            shutil.move(src, dst)
        
        # Cleanup
        shutil.rmtree(temp_dir)
        
        # Start service
        self._print_info("Starting service...")
        self._run_command(['systemctl', 'start', 'pg-api'])
        
        self._print_success("Restore completed successfully")


def main():
    """Main entry point"""
    parser = argparse.ArgumentParser(
        description='PostgreSQL API Service Setup',
        formatter_class=argparse.RawDescriptionHelpFormatter
    )
    
    parser.add_argument(
        '--action',
        choices=['install', 'upgrade', 'uninstall', 'backup', 'restore', 'check'],
        default='install',
        help='Action to perform (default: install)'
    )
    
    parser.add_argument(
        '--non-interactive',
        action='store_true',
        help='Run in non-interactive mode'
    )
    
    parser.add_argument(
        '--install-path',
        default=DEFAULT_INSTALL_PATH,
        help=f'Installation path (default: {DEFAULT_INSTALL_PATH})'
    )
    
    parser.add_argument(
        '--domain',
        help='Domain name for web access'
    )
    
    parser.add_argument(
        '--ssl-cert',
        help='Path to SSL certificate file'
    )
    
    parser.add_argument(
        '--ssl-key',
        help='Path to SSL key file'
    )
    
    parser.add_argument(
        '--postgres-host',
        default='localhost',
        help='PostgreSQL host (default: localhost)'
    )
    
    parser.add_argument(
        '--postgres-port',
        type=int,
        default=5432,
        help='PostgreSQL port (default: 5432)'
    )
    
    parser.add_argument(
        '--opensearch-url',
        help='OpenSearch API URL for logging/metrics'
    )
    
    parser.add_argument(
        '--opensearch-token',
        help='OpenSearch API token'
    )
    
    parser.add_argument(
        '--license-manager-url',
        help='License manager URL'
    )
    
    parser.add_argument(
        '--license-key',
        help='License key'
    )
    
    parser.add_argument(
        '--service-user',
        default=DEFAULT_SERVICE_USER,
        help=f'Service user (default: {DEFAULT_SERVICE_USER})'
    )
    
    parser.add_argument(
        '--service-group',
        default=DEFAULT_SERVICE_GROUP,
        help=f'Service group (default: {DEFAULT_SERVICE_GROUP})'
    )
    
    parser.add_argument(
        '--app-port',
        type=int,
        default=DEFAULT_APP_PORT,
        help=f'Application port (default: {DEFAULT_APP_PORT})'
    )
    
    args = parser.parse_args()
    
    # Check for root privileges for certain operations
    if args.action in ['install', 'uninstall'] and os.geteuid() != 0:
        print(f"{Colors.WARNING}Warning: Running without root privileges. Some operations may fail.{Colors.ENDC}")
        print(f"Consider running with: sudo python3 {' '.join(sys.argv)}")
    
    # Run setup
    setup = PostgreSQLAPISetup(args)
    setup.run()


if __name__ == '__main__':
    main()