# ═══════════════════════════════════════════════════════════════════════════════
# ProxySQL — Connection Pooling + Read/Write Splitting
#
# Hostgroups:
#   0 = WRITER  (mysql-master)   — INSERT / UPDATE / DELETE / DDL
#   10 = READER (replicas)       — SELECT
#
# Todos os SELECTs fora de transacção são roteados para as réplicas.
# SELECTs dentro de BEGIN...COMMIT vão para o master (consistência).
# ═══════════════════════════════════════════════════════════════════════════════

datadir = "/var/lib/proxysql"

admin_variables =
{
  admin_credentials = "admin:admin_proxysql_2025"
  mysql_ifaces      = "0.0.0.0:6032"
  refresh_interval  = 2000
}

mysql_variables =
{
  threads                = 8
  max_connections        = 2048        # máximo de conexões de clientes
  default_query_delay    = 0
  default_query_timeout  = 30000
  have_compress          = true
  poll_timeout           = 2000
  interfaces             = "0.0.0.0:6033"
  default_schema         = "beconnect"
  stacksize              = 1048576
  server_version         = "8.0.36"
  connect_timeout_server = 3000
  monitor_username       = "proxysql_monitor"
  monitor_password       = "proxysql_monitor_2025"
  monitor_history        = 600000
  monitor_connect_interval   = 5000
  monitor_ping_interval      = 3000
  monitor_read_only_interval = 1500
  monitor_read_only_timeout  = 500
  ping_interval_server_msec  = 10000
  ping_timeout_server        = 200
  commands_stats             = true
  sessions_sort              = true
  connect_retries_on_failure = 5
}

# ─── Servidores MySQL ──────────────────────────────────────────────────────────
mysql_servers =
(
  {
    address          = "mysql-master"
    port             = 3306
    hostgroup        = 0              # WRITER
    status           = "ONLINE"
    weight           = 1
    compression      = 0
    max_connections  = 200
    max_replication_lag = 0
    comment          = "master - writes"
  },
  {
    address          = "mysql-replica-1"
    port             = 3306
    hostgroup        = 10             # READER
    status           = "ONLINE"
    weight           = 1
    compression      = 0
    max_connections  = 300
    max_replication_lag = 10          # aceita até 10s de lag
    comment          = "replica-1 - reads"
  },
  {
    address          = "mysql-replica-2"
    port             = 3306
    hostgroup        = 10             # READER
    status           = "ONLINE"
    weight           = 1
    compression      = 0
    max_connections  = 300
    max_replication_lag = 10
    comment          = "replica-2 - reads"
  }
)

# ─── Utilizadores MySQL ────────────────────────────────────────────────────────
mysql_users =
(
  {
    username              = "beconnect"
    password              = "beconnect_secret"
    default_hostgroup     = 0         # por defeito vai para o master
    default_schema        = "beconnect"
    active                = 1
    max_connections       = 1000
    transaction_persistent = 1        # dentro de transacção fica no mesmo servidor
  }
)

# ─── Regras de routing ─────────────────────────────────────────────────────────
# Ordem de avaliação: rule_id crescente (para na primeira que faz match)
mysql_query_rules =
(
  # 1. Forçar master explicitamente (comentário /*master*/)
  {
    rule_id               = 1
    active                = 1
    match_pattern         = "^/\\*master\\*/"
    destination_hostgroup = 0
    apply                 = 1
  },
  # 2. Forçar réplica explicitamente (comentário /*replica*/)
  {
    rule_id               = 2
    active                = 1
    match_pattern         = "^/\\*replica\\*/"
    destination_hostgroup = 10
    apply                 = 1
  },
  # 3. SELECTs para réplicas (fora de transacção)
  {
    rule_id               = 10
    active                = 1
    match_pattern         = "^SELECT"
    destination_hostgroup = 10
    apply                 = 1
  },
  # 4. SELECT ... FOR UPDATE / LOCK IN SHARE MODE → master (consistência)
  {
    rule_id               = 5
    active                = 1
    match_pattern         = "^SELECT.*FOR UPDATE"
    destination_hostgroup = 0
    apply                 = 1
  },
  {
    rule_id               = 6
    active                = 1
    match_pattern         = "^SELECT.*LOCK IN SHARE MODE"
    destination_hostgroup = 0
    apply                 = 1
  },
  # 5. Todo o resto (INSERT, UPDATE, DELETE, DDL) → master
  {
    rule_id               = 100
    active                = 1
    match_pattern         = ".*"
    destination_hostgroup = 0
    apply                 = 1
  }
)

# ─── Monitorização de replicação ───────────────────────────────────────────────
mysql_replication_hostgroups =
(
  {
    writer_hostgroup = 0
    reader_hostgroup = 10
    check_type       = "read_only"
    comment          = "beconnect replication group"
  }
)