#!/bin/bash



# Passo 1: Baixar o arquivo do Prometheus 2.51.1
mkdir -p /instalacoes/prometheus/
cd /instalacoes/prometheus/
wget https://github.com/prometheus/prometheus/releases/download/v2.51.1/prometheus-2.51.1.linux-amd64.tar.gz;
wget https://github.com/prometheus/node_exporter/releases/download/v1.7.0/node_exporter-1.7.0.linux-amd64.tar.gz;
wget https://github.com/free/sql_exporter/releases/download/0.5/sql_exporter-0.5.linux-amd64.tar.gz;


apt install netcat -y

yum install nc

# Passo 2: Extrair o arquivo
tar xvf prometheus-2.51.1.linux-amd64.tar.gz




# Passo 3: Mover os binários para /usr/local/bin

useradd --no-create-home --shell /bin/false prometheus;mkdir /etc/prometheus;mkdir /var/lib/prometheus;chown prometheus:prometheus /etc/prometheus;chown prometheus:prometheus /var/lib/prometheus;tar -xvzf prometheus-2.51.1.linux-amd64.tar.gz;mv prometheus-2.51.1.linux-amd64 prometheuspackage;cp prometheuspackage/prometheus /usr/local/bin/;cp prometheuspackage/promtool /usr/local/bin/;sudo chown prometheus:prometheus /usr/local/bin/prometheus;sudo chown prometheus:prometheus /usr/local/bin/promtool;sudo cp -r prometheuspackage/consoles /etc/prometheus;sudo cp -r prometheuspackage/console_libraries /etc/prometheus;sudo chown -R prometheus:prometheus /etc/prometheus/consoles;sudo chown -R prometheus:prometheus /etc/prometheus/console_libraries;
tar -xvzf node_exporter-1.7.0.linux-amd64.tar.gz;sudo useradd -rs /bin/false nodeusr;sudo mv node_exporter-1.7.0.linux-amd64/node_exporter /usr/local/bin/;
tar -xzvf sql_exporter-0.5.linux-amd64.tar.gz;useradd -rs /bin/false sqlexporter;mv sql_exporter-0.5.linux-amd64/sql_exporter /usr/local/bin/;

mkdir -p /var/lib/node_exporter/textfile_collector/

mkdir -p /etc/prometheus/scripts/


#passo criar os scripts -- padrão 


cat << 'EOF' > /etc/prometheus/scripts/management_prometheus.sh
#!/bin/bash

DIR="/var/lib/node_exporter/textfile_collector"

get_current_minute() {
    echo "$(date +%M)"
}

run_every_two_minutes() {
    /etc/prometheus/scripts/ppegaragem_online.sh > "$DIR/ppegaragem_online_info.prom"
    /etc/prometheus/scripts/porta_status_terminal_wps.sh
    /etc/prometheus/scripts/pkplus_status_info.sh
    /etc/prometheus/scripts/parkingplus_plus_enterprise_client_status.sh
}

run_every_hour() {
    /etc/prometheus/scripts/backup_check_nuvem.sh
    /etc/prometheus/scripts/file_backup_info.sh > "$DIR/file_backup_info.prom"
}

minute=$(get_current_minute)

if (( minute % 2 == 0 )); then
    run_every_two_minutes
fi

if (( minute % 60 == 0 )); then
    run_every_hour
fi
EOF

cat << 'EOF' > /etc/prometheus/scripts/file_backup_info.sh
#!/bin/bash

# Caminho do arquivo
FILE_PATH="/backup/banco/daily.0/postgres/parkingplus.backup"

if [ -e "$FILE_PATH" ]; then
    FILE_SIZE=$(stat -c%s "$FILE_PATH")
    FILE_CREATION_DATE=$(stat -c%Z "$FILE_PATH")

    echo "# HELP file_backup_info Tamanho e data de criação do arquivo de backup"
    echo "# TYPE file_backup_info gauge"
    echo "file_backup_info{type=\"size\"} $FILE_SIZE"
    echo "file_backup_info{type=\"creation_date\"} $FILE_CREATION_DATE"
else
    echo "# HELP file_backup_info Tamanho e data de criação do arquivo de backup"
    echo "# TYPE file_backup_info gauge"
    echo "file_backup_info{type=\"size\"} 0"
    echo "file_backup_info{type=\"creation_date\"} 0"
fi
EOF

cat << 'EOF' > /etc/prometheus/scripts/ppegaragem_online.sh
#!/bin/bash

active_ports=0
inactive_ports=0

directories=$(locate webapps/ROOT | grep '^/WPSBrasil' | grep '/webapps/ROOT$' | sed 's/\/webapps\/ROOT$//')

for dir in $directories; do
    if [ -f "$dir/conf/server.xml" ]; then
        port=$(grep -oP '(?<=<Connector port=")[0-9]+' "$dir/conf/server.xml" | head -n 1)
        if [ -n "$port" ]; then
            if ss -ltn | grep -q "\b$port\b"; then
                active_ports=$((active_ports + 1))
            else
                inactive_ports=$((inactive_ports + 1))
            fi
        fi
    fi
done

cat <<INFO
# HELP active_ports Total de portas TCP ativas configuradas.
# TYPE active_ports gauge
active_ports $active_ports

# HELP inactive_ports Total de portas TCP inativas configuradas.
# TYPE inactive_ports gauge
inactive_ports $inactive_ports
INFO
EOF

cat << 'EOF' > /etc/prometheus/scripts/pkplus_status_info.sh
#!/bin/bash

output_file="/var/lib/node_exporter/textfile_collector/pkplus_service_status.prom"

> "$output_file"

pkplus-cli svc list -a | tail -n +2 | while read -r line; do
    service=$(echo "$line" | awk '{print $1}')
    activated=$(echo "$line" | awk '{print $2}' | grep -q 'Sim' && echo 1 || echo 0)
    running=$(echo "$line" | awk '{print $3}' | grep -q 'Sim' && echo 1 || echo 0)

    # Escreve as métricas no arquivo de saída
    echo "service_status{service=\"$service\",status=\"activated\"} $activated" >> "$output_file"
    echo "service_status{service=\"$service\",status=\"running\"} $running" >> "$output_file"
done
EOF

cat << 'EOF' > /etc/prometheus/scripts/parkingplus_plus_enterprise_client_status.sh
#!/bin/bash

output_file="/var/lib/node_exporter/textfile_collector/parkingplus_plus_enterprise_client_status.prom"

> "$output_file"

live_rpm=$(systemctl is-active parkingplus-enterprise-client)
live_docker=$(/usr/bin/pkplus-cli svc list -a | grep parkingplus-enterprise| grep Sim | awk '{print $2$3}')
live_status=0

if [ "$live_rpm" == "active" ] || [ "$live_docker" == "SimSim" ]; then
    live_status=1
else
    live_status=0
fi

echo "live_operator_status{status=\"running\"} $live_status" >> "$output_file"
EOF

cat << 'EOF' > /etc/prometheus/scripts/porta_status_terminal_wps.sh
#!/bin/bash

output_file="/var/lib/node_exporter/textfile_collector/portas_servicos_equipamentos.prom"

echo "# HELP porta_status Status das portas dos serviços em equipamentos (1 = aberta, 0 = fechada)" > $output_file
echo "# TYPE porta_status gauge" >> $output_file

ids=()
nomes=()
ips=()
tipos=()
ativos=()
status=()

while IFS='|' read -r id nome ip tipo ativo online; do
    ids+=("$id")
    nomes+=("$nome")
    ips+=("$ip")
    tipos+=("$tipo")
    ativos+=("$ativo")
    status+=("$online")
done < <(/usr/bin/docker exec -i $(docker ps --format "{{.ID}} {{.Image}}" | grep 'db:' | awk '{print $1}') \
    psql -U postgres -d parkingplus --pset format=unaligned --pset pager=off \
    -c "select ppm.idestacao, ppm.descricao as terminal, ppm.enderecoip as ip, ppm.tipo as tipo, \
        CASE WHEN p.enderecoip IS NOT NULL THEN 1 ELSE 0 END AS ativo, extract(epoch from p.upsince) as online \
        from ppmestacoes ppm left join ppmestadoestacao p on p.enderecoip=ppm.enderecoip order by 1;" | iconv -f ISO-8859-1 -t UTF-8 | awk 'NR>1' | head -n -1
)

TIMEOUT=2

for i in "${!ips[@]}"; do
    concentrador_status=0
    vnc_status=0
    live_status=0

    if nc -z -w $TIMEOUT "${ips[i]}" 3434; then
        concentrador_status=1
    fi
    if nc -z -w $TIMEOUT "${ips[i]}" 5900; then
        vnc_status=1
    fi
    if nc -z -w $TIMEOUT "${ips[i]}" 9090; then
        live_status=1
    fi

    current_status=$(echo "${status[i]}" | tr -d '\r')

    if [ -z "$current_status" ] || [ "$current_status" == "null" ]; then
        current_status=0 
    fi

    echo "porta_status{ip=\"${ips[i]}\", nome=\"${nomes[i]}\", porta=\"concentrador\", tipo=\"${tipos[i]}\"} $concentrador_status" >> $output_file
    echo "porta_status{ip=\"${ips[i]}\", nome=\"${nomes[i]}\", porta=\"vnc\", tipo=\"${tipos[i]}\"} $vnc_status" >> $output_file
    echo "porta_status{ip=\"${ips[i]}\", nome=\"${nomes[i]}\", porta=\"live\", tipo=\"${tipos[i]}\"} $live_status" >> $output_file
    echo "porta_status{ip=\"${ips[i]}\", nome=\"${nomes[i]}\", porta=\"ativos\", tipo=\"${tipos[i]}\"} ${ativos[i]}" >> $output_file
    echo "porta_status{ip=\"${ips[i]}\", nome=\"${nomes[i]}\", porta=\"status\", tipo=\"${tipos[i]}\"} $current_status" >> $output_file
done
EOF

cat << 'EOF' > /etc/prometheus/scripts/backup_check_nuvem.sh
#!/bin/bash

export LANG=en_US.UTF-8
RCLONE_CONFIG=/root/.config/rclone/rclone.conf
export RCLONE_CONFIG
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

OUTPUT_FILE="/var/lib/node_exporter/textfile_collector/backup_check.prom"

data_atual="`date +%d-%m-%Y`"

# Variáveis de monitoramento
adm="$(cat /etc/monitoramentos/backup_nuvem.sh | grep adm= | head -1 | cut -d '=' -f2 | sed 's/"//g')"
GARAGE="$(cat /etc/monitoramentos/backup_nuvem.sh | grep GARAGEM= | head -1 | cut -d '=' -f2 | sed 's/"//g')"

# Verificação dos checksums
checkrclone="`rclone md5sum gcloudwps:bkps-parkingwps-gcp/"$adm"/"$GARAGE"/"$data_atual"-parkingplus.backup | awk -F ""$data_atual"-parkingplus.backup" '{print $1}'`"
checkgaragemC7="`md5sum /backup/banco/daily.0/postgres/parkingplus.backup | awk -F "/backup/banco/daily.0/postgres/parkingplus.backup" '{print $1}'`"

# Verificar se os checksums coincidem
if [ "$checkrclone" = "$checkgaragemC7" ] ; then
    echo "backup_check_success{type=\"rclone_vs_local\"} 1" > "$OUTPUT_FILE"
else
    echo "backup_check_success{type=\"rclone_vs_local\"} 0" > "$OUTPUT_FILE"
fi
EOF

chmod +x /etc/prometheus/scripts/*


# Passo 4: Criar diretório de configuração e mover o arquivo de configuração
sudo mkdir -p /etc/prometheus
sudo mv /instalacoes/prometheuspackage/prometheus.yml /etc/prometheus/

cat << 'EOF' > /etc/prometheus/lot_data_source.collector.yml
collector_name: lot_data_source
metrics:
  - metric_name: psql_show_effective_cache_size
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [effective_cache_size]
    query: |
      SELECT
      (regexp_replace(
          current_setting('effective_cache_size')::text,
          '\D|\s|GB|MB|kB',
          '',
          'g'
      )::numeric *
      CASE
          WHEN current_setting('effective_cache_size')::text LIKE '%GB' THEN 1024 * 1024 * 1024
          WHEN current_setting('effective_cache_size')::text LIKE '%MB' THEN 1024 * 1024
          ELSE 1
      END
      )::bigint AS "effective_cache_size";

  - metric_name: psql_blocked_transactions
    type: gauge
    help: 'Transacoes bloqueadas no banco.'
    key_labels:
    values: [total]
    query: |
      SELECT
        COUNT(*) as total
        FROM pg_stat_activity 
          WHERE waiting=true;

  - metric_name: psql_show_maintenance_work_mem
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [maintenance_work_mem]
    query: |
      SELECT
      (regexp_replace(
          current_setting('maintenance_work_mem')::text,
          '\D|\s|GB|MB|kB',
          '',
          'g'
      )::numeric *
      CASE
          WHEN current_setting('maintenance_work_mem')::text LIKE '%GB' THEN 1024 * 1024 * 1024
          WHEN current_setting('maintenance_work_mem')::text LIKE '%MB' THEN 1024 * 1024
          ELSE 1
      END
      )::bigint AS "maintenance_work_mem";

  - metric_name: psql_show_max_connections
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [max_connections]
    query: |
      SHOW max_connections;

  - metric_name: psql_show_shared_buffers
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [shared_buffers]
    query: |
      SELECT
      (regexp_replace(
          current_setting('shared_buffers')::text,
          '\D|\s|GB|MB|kB',
          '',
          'g'
      )::numeric *
      CASE
          WHEN current_setting('shared_buffers')::text LIKE '%GB' THEN 1024 * 1024 * 1024
          WHEN current_setting('shared_buffers')::text LIKE '%MB' THEN 1024 * 1024
          ELSE 1
      END
      )::bigint AS "shared_buffers";

  - metric_name: psql_show_wal_buffers
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [wal_buffers]
    query: |
      SELECT
      (regexp_replace(
          current_setting('wal_buffers')::text,
          '\D|\s|GB|MB|kB',
          '',
          'g'
      )::numeric *
      CASE
          WHEN current_setting('wal_buffers')::text LIKE '%GB' THEN 1024 * 1024 * 1024
          WHEN current_setting('wal_buffers')::text LIKE '%MB' THEN 1024 * 1024
          ELSE 1
      END
      )::bigint AS "wal_buffers";

  - metric_name: psql_show_work_mem
    type: gauge
    help: 'Configuracoes postgres.'
    key_labels:
    values: [work_mem]
    query: |
      SELECT
      (regexp_replace(
          current_setting('work_mem')::text,
          '\D|\s|GB|MB|kB',
          '',
          'g'
      )::numeric *
      CASE
          WHEN current_setting('work_mem')::text LIKE '%GB' THEN 1024 * 1024 * 1024
          WHEN current_setting('work_mem')::text LIKE '%MB' THEN 1024 * 1024
          ELSE 1
      END
      )::bigint AS "work_mem";

  - metric_name: psql_total_transactions
    type: gauge
    help: 'Total transacoes ativas no banco.'
    key_labels:
    values: [total]
    query: |
      SELECT count(*) AS total FROM pg_stat_activity;

  - metric_name: psql_total_lot
    type: gauge
    help: 'Total de veiculos no patio.'
    key_labels:
      - descricao
    values: [total]
    query: |
      WITH rotativo AS (
        SELECT 'Rotativo'::TEXT AS descricao, count(*) AS total 
        FROM lot 
        WHERE mensal = 'F' AND veiculo_locadora = false
      ),
      mensal AS (
          SELECT 'Mensal'::TEXT AS descricao, count(*) AS total 
          FROM lot 
          WHERE mensal = 'T'
      ),
      locadora AS (
          SELECT 'Locadora'::TEXT AS descricao, count(*) AS total 
          FROM lot 
          WHERE veiculo_locadora = true
      ),
      total AS (
          SELECT 'Total Geral'::TEXT AS descricao, count(*) AS total 
          FROM lot
      )
      SELECT descricao, total FROM rotativo
      UNION ALL
      SELECT descricao, total FROM mensal
      UNION ALL
      SELECT descricao, total FROM locadora
      UNION ALL
      SELECT descricao, total FROM total;

  - metric_name: psql_lot_mensal
    type: gauge
    help: 'Total de veiculos no patio mensalista.'
    key_labels:
    values: [total]
    query: |
      SELECT count(*) as total
      FROM lot
      WHERE mensal='T';

  - metric_name: psql_lot_avulso
    type: gauge
    help: 'Total de veiculos no patio avulso.'
    key_labels:
    values: [total]
    query: |
      SELECT count(*) as total
      FROM lot
      WHERE mensal='F' AND veiculo_locadora=false;

  - metric_name: psql_total_lot_locadora
    type: gauge
    help: 'Total de veiculos por grupo de locadora'
    key_labels:
      - descricao
    values:
      - total
    query: |
      SELECT COUNT(*) as total,descricao_locadora as descricao
      FROM lot
      WHERE veiculo_locadora=true
      AND descricao_locadora IS NOT NULL
      GROUP BY descricao_locadora;

  - metric_name: psql_lot_setor
    type: gauge
    help: 'Total de veiculos por setor no patio.'
    key_labels:
      - setor
    values:
      - total
    query: |
      SELECT COUNT(*) as total,setor as setor
      FROM lot
      WHERE veiculo_locadora=false AND setor IS NOT NULL AND setor !='' GROUP BY setor;

  - metric_name: psql_sincronizacao_enterprise
    type: gauge
    help: 'Total de dados na tabela.'
    key_labels:
    values:
      - total
    query: |
      SELECT COUNT(*) as total
      FROM sincronizacao_enterprise;

  - metric_name: psql_ticketmobile
    type: gauge
    help: 'Total de dados na tabela.'
    key_labels:
    values:
      - total
    query: |
      SELECT COUNT(*) as total
      FROM ticketmobile;

  - metric_name: psql_total_validacao_academia
    type: gauge
    help: 'Total de validacoes academia.'
    key_labels:
    values:
      - total
    query: |
      SELECT SUM(total) AS total FROM (
        SELECT COUNT(*) AS total FROM log_hiper WHERE data >= date_trunc('day', now()) AND tipo IN('11','12','13')
        UNION ALL
        SELECT COUNT(*) AS total FROM tickets_hiper WHERE datahoravalid >= date_trunc('day', now()) AND tipo IN('11','12','13')) academia

  - metric_name: psql_total_validacao_academia_pagamento_excedente
    type: gauge
    help: 'Total de validacoes academia com pagamento.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      select 
        'Academia' as descricao, count(*) as total
        from etstickets ets
        join log_hiper lh on ets.ticket=lh.ticket 
        join tickets_hiper_tipos tht on tht.tipo=lh.tipo 
        where lh.data >= date_trunc('day', now()) 
        and ets.data >= date_trunc('day', now()) 
        and ets.sg='F'
        and lh.tipo IN('11','12','13');

  - metric_name: psql_total_validacao_academia_sem_excedente
    type: gauge
    help: 'Total de validacoes academia sem pagamento.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      select 'Academia' as descricao,sum(total) as total from (
      select 
        count(*) as total
        from etstickets ets
        join log_hiper lh on ets.ticket=lh.ticket 
        join tickets_hiper_tipos tht on tht.tipo=lh.tipo 
        left join tickets_hiper th on ets.ticket=th.ticket
        where lh.data >= date_trunc('day', now()) 
        and ets.data >= date_trunc('day', now()) 
        and ets.sg='T'
        and lh.tipo IN('11','12','13')
      union ALL
      select 
        count(*) as total
        from lot l
        left join tickets_hiper th on l.ticket_barras=th.ticket
        join tickets_hiper_tipos tht on tht.tipo=th.tipo 
        where th.datahoravalid >= date_trunc('day', now())
        and th.tipo IN('11','12','13')) as total;

  - metric_name: psql_total_validacao_hiper_sem_excedente
    type: gauge
    help: 'Total de validacoes hiper sem pagamento.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      select 'Hiper' as descricao,sum(total) as total from (
      select 
        count(*) as total
        from etstickets ets
        join log_hiper lh on ets.ticket=lh.ticket 
        join tickets_hiper_tipos tht on tht.tipo=lh.tipo 
        left join tickets_hiper th on ets.ticket=th.ticket
        where lh.data >= date_trunc('day', now()) 
        and ets.data >= date_trunc('day', now()) 
        and ets.sg='T'
        and lh.tipo NOT IN('11','12','13')
      union ALL
      select 
        count(*) as total
        from lot l
        left join tickets_hiper th on l.ticket_barras=th.ticket
        join tickets_hiper_tipos tht on tht.tipo=th.tipo 
        where th.datahoravalid >= date_trunc('day', now())
        and th.tipo NOT IN('11','12','13')) as total;

  - metric_name: psql_total_validacao_hiper
    type: gauge
    help: 'Total de validacoes hiper.'
    key_labels:
    values:
      - total
    query: |
      SELECT SUM(total) AS total FROM (
        SELECT COUNT(*) AS total FROM log_hiper WHERE data >= date_trunc('day', now()) AND tipo NOT IN('11','12','13')
        UNION ALL
        SELECT COUNT(*) AS total FROM tickets_hiper WHERE datahoravalid >= date_trunc('day', now()) AND tipo NOT IN('11','12','13')) hiper

  - metric_name: psql_total_validacao_hiper_por_integrador
    type: gauge
    help: 'Total de validacoes hiper por integrador.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      SELECT SUM(total) AS total,academia.descricao FROM (
        SELECT COUNT(*) AS total,coalesce(ic.nome, 'Integrador Indefinido') as descricao FROM log_hiper lg left join integracaowsvalidacao_custumer ic on ic.id=lg.custumer_id where lg.data >= date_trunc('day', now()) group by 2
        UNION ALL
        SELECT COUNT(*) AS total,coalesce(ic.nome, 'Integrador Indefinido') as descricao FROM tickets_hiper th left join integracaowsvalidacao_custumer ic on ic.id=th.custumer_id WHERE datahoravalid >= date_trunc('day', now()) group by 2) academia group by academia.descricao

  - metric_name: psql_table_size
    type: gauge
    help: 'As dez maiores tabelas do banco.'
    key_labels:
      - relation
    values:
      - total_size
    query: |
      SELECT
        relname AS "relation",
        (pg_total_relation_size(C.oid) / (1024 * 1024))::bigint AS "total_size"
      FROM
          pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE
          nspname NOT IN ('pg_catalog', 'information_schema')
          AND C.relkind <> 'i'
          AND nspname !~ '^pg_toast'
      ORDER BY
          pg_total_relation_size(C.oid) DESC
      LIMIT 10;

  - metric_name: psql_lot_por_tiposervico
    type: gauge
    help: 'Total de veiculos no patio por tiposervico.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      select 
        it.descricao,
        count(*) as total 
        from lot l 
        join intref_tiposervico it on l.tiposervico=it.id 
        group by it.descricao order by total desc;

  - metric_name: psql_etstickets_por_tiposervico
    type: gauge
    help: 'Total de passagens por tiposervico.'
    key_labels:
      - descricao
    values:
      - total
    query: |
      select 
        it.descricao,
        count(*) as total 
        from etstickets e 
        join intref_tiposervico it on e.tiposervico=it.id 
        where e.data>= date_trunc('day', now()) 
        group by it.descricao order by total desc;

  - metric_name: psql_movimentacao_por_horario
    type: gauge
    help: 'Total de movimentacao por dia.'
    key_labels:
      - 'Inicio'
      - 'Fim'
    value_label: tipo
    values:
      - entrada
      - saida
    query: |
      select
        TO_CHAR(data, 'YYYY-MM-DD') || ' ' || hora_inicial as "Inicio",
        TO_CHAR(data, 'YYYY-MM-DD') || ' ' || hora_final as "Fim",
        SUM(quantidade_entrada) AS entrada,
        SUM(quantidade_saida) AS saida
        FROM movimento_por_horario mph
        JOIN ppmestacoes p ON mph.terminal = p.idestacao
        WHERE "data" >= date_trunc('day', now())
        AND p.tipo IN (8, 10)
        GROUP BY data, hora_inicial, hora_final
        ORDER BY hora_inicial;

  - metric_name: psql_movimentacao_por_terminal_entrada
    type: gauge
    help: 'Total de passagens por terminal.'
    key_labels:
      - descricao
    values:
      - entrada
    query: |
      select
        p.descricao as descricao, 
        SUM(quantidade_entrada) AS entrada
        FROM movimento_por_horario mph 
        JOIN ppmestacoes p ON mph.terminal = p.idestacao 
        WHERE "data" >= date_trunc('day', now()) 
        AND p.tipo IN (8) 
        GROUP BY p.descricao, p.idestacao 
      union
      select 
              'TOTAL' as descricao,
              SUM(quantidade_entrada) AS entrada
              FROM movimento_por_horario mph 
              JOIN ppmestacoes p ON mph.terminal = p.idestacao 
              WHERE "data" >= date_trunc('day', now()) 
              AND p.tipo IN (8)
      ORDER BY descricao;

  - metric_name: psql_movimentacao_por_terminal_saida
    type: gauge
    help: 'Total de passagens por terminal.'
    key_labels:
      - descricao
    values:
      - saida
    query: |
      select
        p.descricao as descricao, 
        SUM(quantidade_saida) AS saida
        FROM movimento_por_horario mph 
        JOIN ppmestacoes p ON mph.terminal = p.idestacao 
        WHERE "data" >= date_trunc('day', now()) 
        AND p.tipo IN (10) 
        GROUP BY p.descricao,p.idestacao 
      UNION
      select 
        'TOTAL' as descricao,
        SUM(quantidade_saida) AS entrada
        FROM movimento_por_horario mph 
        JOIN ppmestacoes p ON mph.terminal = p.idestacao 
        WHERE "data" >= date_trunc('day', now()) 
        AND p.tipo IN (10)
      ORDER BY descricao;

  - metric_name: psql_movimentacao_por_terminal_entrada_tiposervico
    type: gauge
    help: 'Total de passagens por terminal e tipo de servicos.'
    key_labels:
      - Descricao
      - Tipo
    values:
      - entrada
    query: |
      WITH tipos_usados AS (
        SELECT DISTINCT it.id, it.descricao
        FROM etetickets e
        JOIN intref_tiposervico it ON it.id = e.tiposervico
        WHERE e."data" >= date_trunc('day', now())
      )
      SELECT
        p.descricao AS "Descricao",
        it.descricao AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS entrada
      FROM
        ppmestacoes p
      CROSS JOIN
        tipos_usados it
      LEFT JOIN
        etetickets e ON p.idestacao = e.idterminal 
        AND it.id = e.tiposervico 
        AND e."data" >= date_trunc('day', now())
      WHERE
        p.tipo = '8' 
      GROUP BY
        p.descricao, it.descricao, p.idestacao
      UNION ALL
      SELECT
        p.descricao AS "Descricao",
        'TOTAL TIPOS' AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS entrada
      FROM
        ppmestacoes p
      LEFT JOIN
        etetickets e ON p.idestacao = e.idterminal 
        AND e."data" >= date_trunc('day', now())
      WHERE
        p.tipo = '8'
      GROUP BY p.descricao
      UNION ALL
      SELECT
        'TOTAL' AS "Descricao",
        it.descricao AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS entrada
      FROM
        tipos_usados it
      LEFT JOIN
        etetickets e ON it.id = e.tiposervico 
        AND e."data" >= date_trunc('day', now())
      JOIN
        ppmestacoes p ON p.idestacao = e.idterminal
      WHERE
        p.tipo = '8' 
      GROUP BY
        it.descricao
      UNION ALL
      SELECT
        'TOTAL' AS "Descricao",
        'TOTAL TIPOS' AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS entrada
      FROM
          etetickets e
      JOIN
          ppmestacoes p ON p.idestacao = e.idterminal
      WHERE
          p.tipo = '8' 
      AND e."data" >= date_trunc('day', now())
      ORDER BY "Descricao", "Tipo";

  - metric_name: psql_movimentacao_por_terminal_saida_tiposervico
    type: gauge
    help: 'Total de passagens por terminal e tipo de servicos.'
    key_labels:
      - Descricao
      - Tipo
    values:
      - saida
    query: |
      WITH tipos_usados AS (
        SELECT DISTINCT it.id, it.descricao
        FROM etstickets e
        JOIN intref_tiposervico it ON it.id = e.tiposervico
        WHERE e."data" >= date_trunc('day', now())
      )
      SELECT
        p.descricao AS "Descricao",
        it.descricao AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS saida
      FROM
        ppmestacoes p
      CROSS JOIN
        tipos_usados it
      LEFT JOIN
        etstickets e ON p.idestacao = e.idterminal 
        AND it.id = e.tiposervico 
        AND e."data" >= date_trunc('day', now())
      WHERE
        p.tipo = '10' 
      GROUP BY p.descricao, it.descricao, p.idestacao
      UNION ALL
      SELECT
        p.descricao AS "Descricao",
        'TOTAL TIPOS' AS "Tipo",
        COALESCE(COUNT(e.tipoveiculo), 0) AS saida
      FROM
        ppmestacoes p
      LEFT JOIN
        etstickets e ON p.idestacao = e.idterminal 
        AND e."data" >= date_trunc('day', now())
      WHERE
        p.tipo = '10'
      GROUP BY p.descricao
      UNION ALL
      SELECT
        'TOTAL' AS "Descricao",
        it.descricao AS "Tipo",
        COUNT(e.tipoveiculo) AS saida
      FROM
        tipos_usados it
      LEFT JOIN
        etstickets e ON it.id = e.tiposervico 
        AND e."data" >= date_trunc('day', now())
      JOIN
        ppmestacoes p ON p.idestacao = e.idterminal
      WHERE
        p.tipo = '10' 
      GROUP BY it.descricao
      UNION ALL
      SELECT
        'TOTAL' AS "Descricao",
        'TOTAL TIPOS' AS "Tipo",
        COUNT(e.tipoveiculo) AS saida
      FROM
          etstickets e
      JOIN
          ppmestacoes p ON p.idestacao = e.idterminal
      WHERE
          p.tipo = '10'
      AND e."data" >= date_trunc('day', now())
      ORDER BY "Descricao", "Tipo";

  - metric_name: psql_terminal_tipos_servicos_ativos_entrada
    type: gauge
    help: 'Contabilizacao de todos os tipos de servicos dos terminais, como osa,relay,lpr,intercom e etc.'
    value_label: terminal
    key_labels:
      - descricao
    values:
      - osa_ativa
      - lpr_relay
      - tap_go
      - intercom
      - lpr
    query: |
      select
        p.descricao as descricao,
        case
            when c1.secao = 'CONFIG_ODBT' and c1.chave = 'ATIVAR_ODBT' and CAST(c1.valor AS integer) = 1
            then 1
            else 0
        end as osa_ativa,
        case
            when c2.secao = 'LPR_RELAY_LEITURA' and c2.chave = 'ATIVAR' and CAST(c2.valor AS integer) = 1
            then 1
            else 0
        end as lpr_relay,
        case
            when c3.secao = 'TAP&GO' and c3.chave = 'ATIVAR' and CAST(c3.valor AS integer) = 1
            then 1
            else 0
        end as tap_go,
        case
            when (c4.secao = 'INTERCOM' or c5.secao = 'INTERCOM SIP')
                and c4.chave = 'ATIVO' and (CAST(c4.valor AS integer) = 1 or CAST(c5.valor AS integer) = 1)
            then 1
            else 0
        end as intercom,
        case
            when (c6.secao = 'LPR' and c6.chave = 'ATIVO' and CAST(c6.valor AS integer) = 1)
                or (lc.recebimento_ativo = true)
            then 1
            else 0
        end as lpr
        from ppmestacoes p
        left join configuracoes c1
            on c1.ip = p.enderecoip
            and c1.secao = 'CONFIG_ODBT'
            and c1.chave = 'ATIVAR_ODBT'
        left join configuracoes c2
            on c2.ip = p.enderecoip
            and c2.secao = 'LPR_RELAY_LEITURA'
            and c2.chave = 'ATIVAR'
        left join configuracoes c3
            on c3.ip = p.enderecoip
            and c3.secao = 'TAP&GO'
            and c3.chave = 'ATIVAR'
        left join configuracoes c4
            on c4.ip = p.enderecoip
            and c4.secao = 'INTERCOM'
            and c4.chave = 'ATIVO'
        left join configuracoes c5
            on c5.ip = p.enderecoip
            and c5.secao = 'INTERCOM SIP'
            and c5.chave = 'ATIVO'
        left join configuracoes c6
            on c6.ip = p.enderecoip
            and c6.secao = 'LPR'
            and c6.chave = 'ATIVO'
        left join lpr_concentrador lc
          on lc.estacao_id = p.idestacao
        where p.tipo in (8)
        order by p.idestacao;

  - metric_name: psql_terminal_tipos_servicos_ativos_saida
    type: gauge
    help: 'Contabilizacao de todos os tipos de servicos dos terminais, como osa,relay,lpr,intercom e etc.'
    value_label: terminal
    key_labels:
      - descricao
    values:
      - osa_ativa
      - lpr_relay
      - tap_go
      - intercom
      - lpr
    query: |
      select
        p.descricao as descricao,
        case
            when c1.secao = 'CONFIG_ODBT' and c1.chave = 'ATIVAR_ODBT' and CAST(c1.valor AS integer) = 1
            then 1
            else 0
        end as osa_ativa,
        case
            when c2.secao = 'LPR_RELAY_LEITURA' and c2.chave = 'ATIVAR' and CAST(c2.valor AS integer) = 1
            then 1
            else 0
        end as lpr_relay,
        case
            when c3.secao = 'TAP&GO' and c3.chave = 'ATIVAR' and CAST(c3.valor AS integer) = 1
            then 1
            else 0
        end as tap_go,
        case
            when (c4.secao = 'INTERCOM' or c5.secao = 'INTERCOM SIP')
                and c4.chave = 'ATIVO' and (CAST(c4.valor AS integer) = 1 or CAST(c5.valor AS integer) = 1)
            then 1
            else 0
        end as intercom,
        case
            when (c6.secao = 'LPR' and c6.chave = 'ATIVO' and CAST(c6.valor AS integer) = 1)
                or (lc.recebimento_ativo = true)
            then 1
            else 0
        end as lpr
        from ppmestacoes p
        left join configuracoes c1
            on c1.ip = p.enderecoip
            and c1.secao = 'CONFIG_ODBT'
            and c1.chave = 'ATIVAR_ODBT'
        left join configuracoes c2
            on c2.ip = p.enderecoip
            and c2.secao = 'LPR_RELAY_LEITURA'
            and c2.chave = 'ATIVAR'
        left join configuracoes c3
            on c3.ip = p.enderecoip
            and c3.secao = 'TAP&GO'
            and c3.chave = 'ATIVAR'
        left join configuracoes c4
            on c4.ip = p.enderecoip
            and c4.secao = 'INTERCOM'
            and c4.chave = 'ATIVO'
        left join configuracoes c5
            on c5.ip = p.enderecoip
            and c5.secao = 'INTERCOM SIP'
            and c5.chave = 'ATIVO'
        left join configuracoes c6
            on c6.ip = p.enderecoip
            and c6.secao = 'LPR'
            and c6.chave = 'ATIVO'
        left join lpr_concentrador lc
          on lc.estacao_id = p.idestacao
        where p.tipo in (10)
        order by p.idestacao;
EOF

cat << 'EOF' >> /etc/prometheus/lpr_data_source.collector.yml
collector_name: lpr_data_source
metrics:
  - metric_name: psql_lpr_relat_garagem_dia_atual
    type: gauge
    help: 'Coleta do dia atual da lpr - relatorio manager'
    value_label: lpr
    key_labels:
      - terminal
    values:
      - perc_rotativo
      - perc_cred
      - perc_total
      - total_nao_reconhecido_rotativo
      - total_reconhecido_rotativo
      - total_nao_reconhecido_cred
      - total_reconhecido_cred
      - total_nao_reconhecido_total
      - total_reconhecido_total
    query: >
      select * from view_eficiencia_zabbix;

  - metric_name: psql_lpr_relat_garagem_1dia
    type: gauge
    help: Coleta de LPR com 1 dias de calculo - relatorio manager
    value_label: lpr
    key_labels:
      - terminal
    values:
      - perc_rotativo
      - perc_cred
      - perc_total
      - total_nao_reconhecido_rotativo
      - total_reconhecido_rotativo
      - total_nao_reconhecido_cred
      - total_reconhecido_cred
      - total_nao_reconhecido_total
      - total_reconhecido_total
    query: |
      select * from view_eficiencia_zabbix_1dia;

  - metric_name: psql_lpr_relat_garagem_ultimos_cem_veiculos
    type: gauge
    help: Coleta de LPR com ultimas 100 passagens rotativo - relatorio manager
    value_label: lpr
    key_labels:
      - terminal
    values:
      - perc_rotativo
      - perc_cred
      - perc_total
      - total_nao_reconhecido_rotativo
      - total_reconhecido_rotativo
      - total_nao_reconhecido_cred
      - total_reconhecido_cred
      - total_nao_reconhecido_total
      - total_reconhecido_total
    query: |
      select * from view_eficiencia_zabbix_ultimas_pasagens;
EOF


# Variáveis de configuração padrão
ip_banco=""
garagem=""
empresa=""
estado=""
senhabanco=""

# Função para capturar entrada do usuário com valor padrão
get_input() {
  read -p "$1 [$2]: " input
  echo "${input:-$2}"
}

# Função para configurar servidores LPR
configure_lpr() {
  read -p "Existem servidores LPR? (s/n) [n]: " has_lpr
  if [[ "$has_lpr" =~ ^[Ss]$ ]]; then
    read -p "Quantos servidores LPR existem? " lpr_count
    for ((i=1; i<=lpr_count; i++)); do
      read -p "Digite o IP do servidor LPR $i: " lpr_ip
      lpr_ips+=("$lpr_ip")
    done
  fi
}

# Captura as variáveis principais
ip_banco=$(get_input "IP do banco de dados" "$ip_banco")
garagem=$(get_input "Nome da garagem" "$garagem")
empresa=$(get_input "Nome da empresa" "$empresa")
estado=$(get_input "Estado" "$estado")
senhabanco=$(get_input "senha banco" "$senhabanco")


# Configura os servidores LPR
declare -a lpr_ips
configure_lpr

# Gera o arquivo de configuração do Prometheus
cat <<EOF | sudo tee /etc/prometheus/prometheus.yml
global:
  scrape_interval: 30s

scrape_configs:
  - job_name: 'prometheus_master'
    scrape_interval: 10s
    static_configs:
      - targets: 
          - '${ip_banco}:9090'

  - job_name: 'node_exporter_centos'
    scrape_interval: 10s
    static_configs:
      - targets: 
          - '${ip_banco}:9100'
        labels:
          source: 'Servidor'
          garagem: '${garagem}'
          empresa: '${empresa}'
          estado: '${estado}'

  - job_name: 'sql_exporter_centos'
    scrape_interval: 60s
    static_configs:
      - targets: 
          - '${ip_banco}:9399'
        labels:
          source: 'Servidor'
          garagem: '${garagem}'
          empresa: '${empresa}'
          estado: '${estado}'

  - job_name: 'federated_prometheus'
    honor_labels: true
    metrics_path: '/federate'
    params:
      match[]:
        - '{job="prometheus_master"}'
        - '{job="node_exporter_centos"}'
        - '{job="nvidia"}'
    static_configs:
      - targets: 
          - '${ip_banco}:9090'
        labels:
          source: 'Servidor'
          garagem: '${garagem}'
          empresa: '${empresa}'
          estado: '${estado}'
EOF

# Adiciona os servidores LPR no federated_prometheus
for ((i=0; i<${#lpr_ips[@]}; i++)); do
  lpr_ip="${lpr_ips[$i]}"
  lpr_name="Servidor LPR $((i + 1))"
  cat <<EOF | sudo tee -a /etc/prometheus/prometheus.yml

      - targets: 
          - '${lpr_ip}:9090'
        labels:
          source: '${lpr_name}'
          garagem: '${garagem}'
          empresa: '${empresa}'
          estado: '${estado}'
EOF
done

echo "global:
  scrape_timeout_offset: 500ms
  min_interval: 120s
  max_connections: 3
  max_idle_connections: 3
target:
  data_source_name: 'postgres://postgres:${senhabanco}@127.0.0.1:5432/parkingplus?sslmode=disable'
  collectors: [lot_data_source,lpr_data_source]
collector_files:
  - '*.collector.yml'" > /etc/prometheus/sql_exporter.yml


chown prometheus:prometheus /etc/prometheus/prometheus.yml

echo "Arquivo de configuração do Prometheus gerado em /etc/prometheus/prometheus.yml"



# Passo 6: Criar serviço systemd
echo "[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target
[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus \
--config.file /etc/prometheus/prometheus.yml \
--storage.tsdb.path /var/lib/prometheus/ \
--web.console.templates=/etc/prometheus/consoles \
--storage.tsdb.retention.time=1h \
--web.console.libraries=/etc/prometheus/console_libraries
[Install]
WantedBy=multi-user.target" > /etc/systemd/system/prometheus.service;

echo "[Unit]
Description=Node Exporter
After=network.target
[Service]
User=nodeusr
Group=nodeusr
Type=simple
ExecStart=/usr/local/bin/node_exporter --collector.textfile.directory=/var/lib/node_exporter/textfile_collector
[Install]
WantedBy=multi-user.target" > /etc/systemd/system/node_exporter.service;

echo "[Unit]
Description=SQL Exporter
After=network.target

[Service]
User=sqlexporter
Group=sqlexporter
Type=simple
ExecStart=/usr/local/bin/sql_exporter -config.file=/etc/prometheus/sql_exporter.yml

[Install]
WantedBy=multi-user.target" > /etc/systemd/system/sql_exporter.service



systemctl daemon-reload;systemctl start sql_exporter;systemctl enable sql_exporter;systemctl restart sql_exporter;rm -rf /tmp/sql_exporter.*

  systemctl start node_exporter;systemctl enable node_exporter;systemctl restart node_exporter
  systemctl enable prometheus;systemctl restart prometheus