# Production PostgreSQL Deployment with High Availability
# Phase 3: Production Database Infrastructure

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: janua-production
data:
  POSTGRES_DB: "janua"
  POSTGRES_USER: "janua"
  # PostgreSQL Configuration Optimizations
  postgresql.conf: |
    # Connection Settings
    max_connections = 200
    shared_buffers = 256MB
    effective_cache_size = 1GB
    
    # Memory Configuration
    work_mem = 4MB
    maintenance_work_mem = 64MB
    
    # Checkpoint Configuration
    checkpoint_completion_target = 0.9
    wal_buffers = 16MB
    
    # Query Performance
    random_page_cost = 1.1
    effective_io_concurrency = 200
    
    # Logging Configuration
    log_min_duration_statement = 100ms
    log_statement = 'ddl'
    log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
    
    # Replication (for future HA setup)
    wal_level = replica
    max_wal_senders = 3
    max_replication_slots = 3
    
    # Performance Monitoring
    shared_preload_libraries = 'pg_stat_statements'
    pg_stat_statements.track = all
    pg_stat_statements.save = on

---
apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: janua-production
type: Opaque
stringData:
  POSTGRES_PASSWORD: "REPLACE_WITH_SECURE_PASSWORD"
  # Database URL for applications
  DATABASE_URL: "postgresql://janua:REPLACE_WITH_SECURE_PASSWORD@postgres-service:5432/janua"

---
# PostgreSQL StatefulSet for Production
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: janua-production
  labels:
    app: postgres
    component: database
spec:
  serviceName: postgres-service
  replicas: 1  # Single instance for now, can be upgraded to HA later
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
        component: database
    spec:
      securityContext:
        fsGroup: 999
        runAsUser: 999
        runAsGroup: 999
      
      containers:
      - name: postgres
        image: postgres:15-alpine
        ports:
        - containerPort: 5432
          name: postgres
        
        envFrom:
        - configMapRef:
            name: postgres-config
        - secretRef:
            name: postgres-secret
        
        resources:
          requests:
            cpu: 500m
            memory: 1Gi
          limits:
            cpu: 2000m
            memory: 4Gi
        
        # Health Checks
        livenessProbe:
          exec:
            command:
            - pg_isready
            - -U
            - janua
            - -d
            - janua
          initialDelaySeconds: 30
          periodSeconds: 10
          timeoutSeconds: 5
          failureThreshold: 3
        
        readinessProbe:
          exec:
            command:
            - pg_isready
            - -U
            - janua
            - -d
            - janua
          initialDelaySeconds: 5
          periodSeconds: 5
          timeoutSeconds: 3
          successThreshold: 1
          failureThreshold: 3
        
        # Volume Mounts
        volumeMounts:
        - name: postgres-data
          mountPath: /var/lib/postgresql/data
        - name: postgres-config
          mountPath: /etc/postgresql/postgresql.conf
          subPath: postgresql.conf
        
        # Security Context
        securityContext:
          runAsNonRoot: true
          runAsUser: 999
          allowPrivilegeEscalation: false
          capabilities:
            drop:
            - ALL
      
      volumes:
      - name: postgres-config
        configMap:
          name: postgres-config
  
  volumeClaimTemplates:
  - metadata:
      name: postgres-data
    spec:
      accessModes: ["ReadWriteOnce"]
      storageClassName: "fast-ssd"  # Use SSD storage for better performance
      resources:
        requests:
          storage: 100Gi

---
# PostgreSQL Service
apiVersion: v1
kind: Service
metadata:
  name: postgres-service
  namespace: janua-production
  labels:
    app: postgres
    component: database
spec:
  type: ClusterIP
  ports:
  - port: 5432
    targetPort: 5432
    protocol: TCP
    name: postgres
  selector:
    app: postgres

---
# PostgreSQL Exporter for Monitoring
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-exporter
  namespace: janua-production
  labels:
    app: postgres-exporter
    component: monitoring
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres-exporter
  template:
    metadata:
      labels:
        app: postgres-exporter
        component: monitoring
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9187"
    spec:
      containers:
      - name: postgres-exporter
        image: prometheuscommunity/postgres-exporter:latest
        ports:
        - containerPort: 9187
          name: metrics
        env:
        - name: DATA_SOURCE_NAME
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: DATABASE_URL
        resources:
          requests:
            cpu: 100m
            memory: 128Mi
          limits:
            cpu: 200m
            memory: 256Mi
        securityContext:
          runAsNonRoot: true
          runAsUser: 65534
          allowPrivilegeEscalation: false
          readOnlyRootFilesystem: true
          capabilities:
            drop:
            - ALL

---
# PostgreSQL Exporter Service
apiVersion: v1
kind: Service
metadata:
  name: postgres-exporter-service
  namespace: janua-production
  labels:
    app: postgres-exporter
    component: monitoring
spec:
  type: ClusterIP
  ports:
  - port: 9187
    targetPort: 9187
    protocol: TCP
    name: metrics
  selector:
    app: postgres-exporter