AWS RDS PostgreSQL

# ─────────────────────────────────────────────────────────────────────────────
# AWS RDS PostgreSQL (Multi-AZ, encrypted, automated backups)
# ─────────────────────────────────────────────────────────────────────────────

variable "environment"        { default = "production" }
variable "db_name"            { default = "appdb" }
variable "db_username"        { default = "appuser" }
variable "db_password"        { sensitive = true }
variable "instance_class"     { default = "db.t3.medium" }
variable "allocated_storage"  { default = 20 }
variable "private_subnet_ids" { type = list(string) }
variable "vpc_id"             {}
variable "app_sg_id"          {}  # Security group of the application tier

# ── DB Subnet Group ───────────────────────────────────────────────────────────
resource "aws_db_subnet_group" "main" {
  name       = "${var.environment}-db-subnet-group"
  subnet_ids = var.private_subnet_ids
  tags       = { Environment = var.environment }
}

# ── Security Group for RDS ────────────────────────────────────────────────────
resource "aws_security_group" "rds" {
  name        = "${var.environment}-rds-sg"
  description = "PostgreSQL access from application tier"
  vpc_id      = var.vpc_id

  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [var.app_sg_id]
    description     = "PostgreSQL from app tier"
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = { Environment = var.environment }
}

# ── Parameter Group ───────────────────────────────────────────────────────────
resource "aws_db_parameter_group" "postgres" {
  name   = "${var.environment}-postgres16"
  family = "postgres16"

  parameter {
    name  = "log_connections"
    value = "1"
  }

  parameter {
    name  = "log_min_duration_statement"
    value = "1000"  # Log queries > 1s
  }

  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements"
  }
}

# ── RDS Instance ──────────────────────────────────────────────────────────────
resource "aws_db_instance" "main" {
  identifier        = "${var.environment}-postgres"
  engine            = "postgres"
  engine_version    = "16.2"
  instance_class    = var.instance_class
  allocated_storage = var.allocated_storage

  db_name  = var.db_name
  username = var.db_username
  password = var.db_password

  db_subnet_group_name   = aws_db_subnet_group.main.name
  parameter_group_name   = aws_db_parameter_group.postgres.name
  vpc_security_group_ids = [aws_security_group.rds.id]

  # High availability
  multi_az = true

  # Storage
  storage_type          = "gp3"
  storage_encrypted     = true
  max_allocated_storage = 100  # Enable autoscaling up to 100GB

  # Backups
  backup_retention_period   = 7
  backup_window             = "03:00-04:00"
  maintenance_window        = "Mon:04:00-Mon:05:00"
  delete_automated_backups  = false
  skip_final_snapshot       = false
  final_snapshot_identifier = "${var.environment}-postgres-final"

  # Protection
  deletion_protection = true

  # Monitoring
  monitoring_interval          = 60
  performance_insights_enabled = true

  tags = { Environment = var.environment }
}

output "db_endpoint" { value = aws_db_instance.main.endpoint }
output "db_name"     { value = aws_db_instance.main.db_name }