#!/usr/bin/env bash
set -Eeuo pipefail
umask 022

# STEP014 - SERVER ATTESO: 215.6 / ax-accessi
# Audit completo dei dati DEMO in presenze_distribuite.terminal_clockings.
# Nessuna modifica a file, database o servizi.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP014_2156_DISTRIBUITE_TERMINAL_CLOCKINGS_DEMO_FULL_COHERENCE_AUDIT_ONLY_${STAMP}"
WORKDIR="${START_DIR}/.${BASE}_work_$$"
OUTZIP="${START_DIR}/${BASE}.zip"
LOGFILE="${WORKDIR}/run.log"
REPORT="${WORKDIR}/report.txt"
PACKAGED=0
FAILURES=0
WARNINGS=0

EXPECTED_HOST="${STEP_EXPECTED_HOST:-ax-accessi}"
EXPECTED_IP="${STEP_EXPECTED_IP:-192.168.215.6}"
SKIP_SERVER_CHECK="${STEP_SKIP_SERVER_CHECK:-0}"
TARGET_DB="${STEP_TARGET_DB:-presenze_distribuite}"

mkdir -p "$WORKDIR"
: >"$LOGFILE"
: >"$REPORT"

log()    { printf '%s\n' "$*" | tee -a "$LOGFILE"; }
report() { printf '%s\n' "$*" >>"$REPORT"; }
fail()   { FAILURES=$((FAILURES + 1)); log "ERRORE: $*"; report "ERRORE: $*"; }
warn()   { WARNINGS=$((WARNINGS + 1)); log "ATTENZIONE: $*"; report "ATTENZIONE: $*"; }

package_results() {
    local rc="${1:-0}"
    local zip_rc=0

    [[ "$PACKAGED" -eq 1 ]] && return 0
    PACKAGED=1

    report ""
    report "Esito finale: errori=${FAILURES}, avvisi=${WARNINGS}, exit_code=${rc}"

    rm -f -- "$OUTZIP"

    if ! command -v zip >/dev/null 2>&1; then
        rm -rf -- "$WORKDIR"
        printf '%s\n' "ERRORE: comando zip non disponibile" >&2
        return 127
    fi

    (cd "$WORKDIR" && zip -qr "$OUTZIP" .) || zip_rc=$?

    if [[ "$zip_rc" -eq 0 ]]; then
        zip -T "$OUTZIP" >>"$LOGFILE" 2>&1 || zip_rc=$?
    fi

    rm -rf -- "$WORKDIR"

    if [[ "$zip_rc" -ne 0 ]]; then
        rm -f -- "$OUTZIP"
        printf '%s\n' "ERRORE: creazione/verifica ZIP fallita (rc=${zip_rc})" >&2
        return "$zip_rc"
    fi

    printf '%s\n' "ZIP CREATO E VERIFICATO: $OUTZIP"
}

on_exit() {
    local rc=$?
    trap - EXIT INT TERM HUP
    package_results "$rc" || rc=$?
    exit "$rc"
}

trap on_exit EXIT
trap 'exit 130' INT
trap 'exit 143' TERM HUP

log "$BASE"
log "SERVER ATTESO: 215.6 / ax-accessi"
log "DATABASE: $TARGET_DB"
log "MODALITA': AUDIT ONLY - NESSUNA MODIFICA"
log "Avvio: $(date --iso-8601=seconds)"

ACTUAL_HOST="$(hostname -s 2>/dev/null || hostname)"
ACTUAL_IPS="$(hostname -I 2>/dev/null | xargs || true)"

{
    echo "STEP: $BASE"
    echo "Server atteso: 215.6 / ax-accessi"
    echo "Database: $TARGET_DB"
    echo "Modalita': AUDIT ONLY"
    echo "Data: $(date --iso-8601=seconds)"
    echo "Hostname: $ACTUAL_HOST"
    echo "IP: $ACTUAL_IPS"
    echo
} >>"$REPORT"

for cmd in grep awk wc sort zip hostname xargs tr; do
    command -v "$cmd" >/dev/null 2>&1 || fail "comando richiesto non disponibile: $cmd"
done

[[ "$FAILURES" -eq 0 ]] || exit 20

if [[ "$SKIP_SERVER_CHECK" != "1" ]]; then
    [[ "$ACTUAL_HOST" == "$EXPECTED_HOST" ]] \
        || fail "hostname ${ACTUAL_HOST}; previsto ${EXPECTED_HOST}"

    if [[ -n "$EXPECTED_IP" ]] && ! grep -qw -- "$EXPECTED_IP" <<<"$ACTUAL_IPS"; then
        fail "IP ${EXPECTED_IP} non presente tra: ${ACTUAL_IPS}"
    fi
fi

[[ "$FAILURES" -eq 0 ]] || exit 21

MYSQL_BIN="${STEP_MYSQL_BIN:-}"

if [[ -z "$MYSQL_BIN" ]]; then
    if command -v mysql >/dev/null 2>&1; then
        MYSQL_BIN="$(command -v mysql)"
    elif command -v mariadb >/dev/null 2>&1; then
        MYSQL_BIN="$(command -v mariadb)"
    else
        fail "client mysql/mariadb non trovato"
        exit 22
    fi
fi

MYSQL=("$MYSQL_BIN" --batch --raw --default-character-set=utf8mb4)
MYSQL_SCALAR=("$MYSQL_BIN" --batch --raw --skip-column-names --default-character-set=utf8mb4)

if ! "${MYSQL_SCALAR[@]}" -e 'SELECT 1' >/dev/null 2>&1; then
    fail "accesso MySQL locale non riuscito"
    exit 23
fi

sql_scalar() {
    "${MYSQL_SCALAR[@]}" -e "$1" 2>>"$LOGFILE"
}

DB_EXISTS="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME='${TARGET_DB}';")"

TABLE_EXISTS="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings';")"

[[ "$DB_EXISTS" == "1" ]] || fail "database assente: $TARGET_DB"
[[ "$TABLE_EXISTS" == "1" ]] || fail "tabella assente: ${TARGET_DB}.terminal_clockings"
[[ "$FAILURES" -eq 0 ]] || exit 24

log "[1/7] Riepilogo generale"

TOTAL_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings;")"

DEMO_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO';")"

DEMO_BADGES="$(sql_scalar "
SELECT COUNT(DISTINCT BadgeNumber)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO';")"

DATE_MIN="$(sql_scalar "
SELECT COALESCE(DATE_FORMAT(MIN(DateAndTime),'%Y-%m-%d %H:%i:%s'),'')
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO';")"

DATE_MAX="$(sql_scalar "
SELECT COALESCE(DATE_FORMAT(MAX(DateAndTime),'%Y-%m-%d %H:%i:%s'),'')
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO';")"

SENSE_0="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
  AND Sense='0';")"

SENSE_1="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
  AND Sense='1';")"

OTHER_SENSE="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
  AND Sense NOT IN ('0','1');")"

{
    printf 'voce\tvalore\n'
    printf 'righe_totali\t%s\n' "$TOTAL_ROWS"
    printf 'righe_demo\t%s\n' "$DEMO_ROWS"
    printf 'badge_demo_distinti\t%s\n' "$DEMO_BADGES"
    printf 'data_minima_demo\t%s\n' "$DATE_MIN"
    printf 'data_massima_demo\t%s\n' "$DATE_MAX"
    printf 'sense_0\t%s\n' "$SENSE_0"
    printf 'sense_1\t%s\n' "$SENSE_1"
    printf 'sense_diverso_0_1\t%s\n' "$OTHER_SENSE"
} >"${WORKDIR}/01_GENERAL_SUMMARY.tsv"

[[ "$DEMO_ROWS" -gt 0 ]] || fail "nessuna riga DEMO"
[[ "$DEMO_BADGES" -gt 0 ]] || fail "nessun badge DEMO"
[[ "$OTHER_SENSE" == "0" ]] || warn "righe DEMO con Sense diverso da 0/1: $OTHER_SENSE"

[[ "$FAILURES" -eq 0 ]] || exit 25

log "[2/7] Riepilogo per badge"

"${MYSQL[@]}" -e "
SELECT
    BadgeNumber,
    COUNT(*) AS righe,
    SUM(Sense='0') AS sense_0,
    SUM(Sense='1') AS sense_1,
    COUNT(DISTINCT DATE(DateAndTime)) AS giorni,
    MIN(DateAndTime) AS prima_timbratura,
    MAX(DateAndTime) AS ultima_timbratura,
    MIN(TIME(DateAndTime)) AS primo_orario,
    MAX(TIME(DateAndTime)) AS ultimo_orario
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
GROUP BY BadgeNumber
ORDER BY BadgeNumber;" >"${WORKDIR}/02_BY_BADGE.tsv" 2>>"$LOGFILE"

log "[3/7] Distribuzione per giorno/badge"

"${MYSQL[@]}" -e "
SELECT
    BadgeNumber,
    DATE(DateAndTime) AS giorno,
    COUNT(*) AS righe,
    SUM(Sense='0') AS sense_0,
    SUM(Sense='1') AS sense_1,
    MIN(TIME(DateAndTime)) AS primo_orario,
    MAX(TIME(DateAndTime)) AS ultimo_orario
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
GROUP BY BadgeNumber,DATE(DateAndTime)
ORDER BY BadgeNumber,giorno;" >"${WORKDIR}/03_BY_BADGE_DAY.tsv" 2>>"$LOGFILE"

ODD_DAYS="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT BadgeNumber,DATE(DateAndTime) AS giorno,COUNT(*) AS righe
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE infoData='DEMO'
    GROUP BY BadgeNumber,DATE(DateAndTime)
    HAVING MOD(COUNT(*),2)<>0
) x;")"

MISSING_SENSE_DAYS="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT
        BadgeNumber,
        DATE(DateAndTime) AS giorno,
        SUM(Sense='0') AS ingressi,
        SUM(Sense='1') AS uscite
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE infoData='DEMO'
    GROUP BY BadgeNumber,DATE(DateAndTime)
    HAVING SUM(Sense='0')=0
        OR SUM(Sense='1')=0
) x;")"

UNBALANCED_DAYS="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT
        BadgeNumber,
        DATE(DateAndTime) AS giorno,
        SUM(Sense='0') AS ingressi,
        SUM(Sense='1') AS uscite
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE infoData='DEMO'
    GROUP BY BadgeNumber,DATE(DateAndTime)
    HAVING SUM(Sense='0')<>SUM(Sense='1')
) x;")"

INVALID_ORDER_DAYS="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT
        BadgeNumber,
        DATE(DateAndTime) AS giorno,
        MIN(CASE WHEN Sense='0' THEN DateAndTime END) AS primo_ingresso,
        MAX(CASE WHEN Sense='1' THEN DateAndTime END) AS ultima_uscita
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE infoData='DEMO'
    GROUP BY BadgeNumber,DATE(DateAndTime)
    HAVING primo_ingresso IS NOT NULL
       AND ultima_uscita IS NOT NULL
       AND primo_ingresso>=ultima_uscita
) x;")"

{
    printf 'controllo\tvalore\n'
    printf 'giorni_con_numero_dispari_righe\t%s\n' "$ODD_DAYS"
    printf 'giorni_senza_ingresso_o_uscita\t%s\n' "$MISSING_SENSE_DAYS"
    printf 'giorni_ingressi_uscite_sbilanciati\t%s\n' "$UNBALANCED_DAYS"
    printf 'giorni_con_ordine_temporale_invalido\t%s\n' "$INVALID_ORDER_DAYS"
} >"${WORKDIR}/04_DAILY_COHERENCE.tsv"

log "[4/7] Duplicati esatti"

EXACT_DUPLICATES="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT BadgeNumber,DateAndTime,COUNT(*) AS copie
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE infoData='DEMO'
    GROUP BY BadgeNumber,DateAndTime
    HAVING COUNT(*)>1
) x;")"

"${MYSQL[@]}" -e "
SELECT
    BadgeNumber,
    DateAndTime,
    COUNT(*) AS copie,
    GROUP_CONCAT(Id ORDER BY Id) AS ids
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
GROUP BY BadgeNumber,DateAndTime
HAVING COUNT(*)>1
ORDER BY BadgeNumber,DateAndTime
LIMIT 500;" >"${WORKDIR}/05_EXACT_DUPLICATES.tsv" 2>>"$LOGFILE"

[[ "$EXACT_DUPLICATES" == "0" ]] || warn "duplicati BadgeNumber+DateAndTime: $EXACT_DUPLICATES"

log "[5/7] Badge attivi senza timbrature e badge orfani"

ACTIVE_WITHOUT_CLOCKINGS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_associazioni_temporali a
JOIN \`${TARGET_DB}\`.ax_user u
  ON u.usrId=a.usrId
 AND u.usrActive='1'
WHERE a.attuale='1'
  AND a.usrBdg IS NOT NULL
  AND TRIM(a.usrBdg)<>''
  AND NOT EXISTS (
      SELECT 1
      FROM \`${TARGET_DB}\`.terminal_clockings t
      WHERE t.infoData='DEMO'
        AND t.BadgeNumber=a.usrBdg
  );")"

ORPHAN_CLOCKING_BADGES="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT DISTINCT t.BadgeNumber
    FROM \`${TARGET_DB}\`.terminal_clockings t
    WHERE t.infoData='DEMO'
      AND NOT EXISTS (
          SELECT 1
          FROM \`${TARGET_DB}\`.ax_associazioni_temporali a
          JOIN \`${TARGET_DB}\`.ax_user u
            ON u.usrId=a.usrId
           AND u.usrActive='1'
          WHERE a.attuale='1'
            AND a.usrBdg=t.BadgeNumber
      )
) x;")"

{
    printf 'controllo\tvalore\n'
    printf 'badge_attivi_senza_timbrature_demo\t%s\n' "$ACTIVE_WITHOUT_CLOCKINGS"
    printf 'badge_timbrature_demo_senza_utente_attivo\t%s\n' "$ORPHAN_CLOCKING_BADGES"
} >"${WORKDIR}/06_BADGE_COHERENCE.tsv"

[[ "$ACTIVE_WITHOUT_CLOCKINGS" == "0" ]] \
    || warn "badge attivi senza timbrature DEMO: $ACTIVE_WITHOUT_CLOCKINGS"

[[ "$ORPHAN_CLOCKING_BADGES" == "0" ]] \
    || warn "badge DEMO senza utente attivo: $ORPHAN_CLOCKING_BADGES"

log "[6/7] Dettaglio anomalie"

"${MYSQL[@]}" -e "
SELECT
    BadgeNumber,
    DATE(DateAndTime) AS giorno,
    COUNT(*) AS righe,
    SUM(Sense='0') AS ingressi,
    SUM(Sense='1') AS uscite,
    GROUP_CONCAT(
        CONCAT(TIME(DateAndTime),'/',Sense)
        ORDER BY DateAndTime
        SEPARATOR ', '
    ) AS sequenza
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
GROUP BY BadgeNumber,DATE(DateAndTime)
HAVING MOD(COUNT(*),2)<>0
    OR SUM(Sense='0')=0
    OR SUM(Sense='1')=0
    OR SUM(Sense='0')<>SUM(Sense='1')
ORDER BY BadgeNumber,giorno
LIMIT 1000;" >"${WORKDIR}/07_DAILY_ANOMALIES.tsv" 2>>"$LOGFILE"

"${MYSQL[@]}" -e "
SELECT
    Sense,
    Type,
    Reason,
    TerminalId,
    COUNT(*) AS righe,
    COUNT(DISTINCT BadgeNumber) AS badge,
    MIN(DateAndTime) AS data_minima,
    MAX(DateAndTime) AS data_massima
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE infoData='DEMO'
GROUP BY Sense,Type,Reason,TerminalId
ORDER BY Sense,Type,Reason,TerminalId;" >"${WORKDIR}/08_VALUE_DISTRIBUTION.tsv" 2>>"$LOGFILE"

log "[7/7] Esito"

if [[ "$ODD_DAYS" -gt 0 ]]; then
    warn "giorni/badge con numero dispari di righe: $ODD_DAYS"
fi

if [[ "$MISSING_SENSE_DAYS" -gt 0 ]]; then
    warn "giorni/badge senza ingresso o uscita: $MISSING_SENSE_DAYS"
fi

if [[ "$UNBALANCED_DAYS" -gt 0 ]]; then
    warn "giorni/badge con ingressi e uscite sbilanciati: $UNBALANCED_DAYS"
fi

if [[ "$INVALID_ORDER_DAYS" -gt 0 ]]; then
    warn "giorni/badge con ingresso non precedente all'uscita: $INVALID_ORDER_DAYS"
fi

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Righe DEMO: $DEMO_ROWS"
    echo "Badge DEMO: $DEMO_BADGES"
    echo "Intervallo: $DATE_MIN -> $DATE_MAX"
    echo "Sense 0: $SENSE_0"
    echo "Sense 1: $SENSE_1"
    echo "Duplicati esatti: $EXACT_DUPLICATES"
    echo "Giorni dispari: $ODD_DAYS"
    echo "Giorni senza ingresso/uscita: $MISSING_SENSE_DAYS"
    echo "Giorni sbilanciati: $UNBALANCED_DAYS"
    echo "Ordine temporale invalido: $INVALID_ORDER_DAYS"
    echo "Badge attivi senza timbrature: $ACTIVE_WITHOUT_CLOCKINGS"
    echo "Badge orfani: $ORPHAN_CLOCKING_BADGES"
    echo "Nessuna modifica eseguita."
} >"${WORKDIR}/99_SUMMARY.txt"

report "Righe DEMO: $DEMO_ROWS"
report "Badge DEMO: $DEMO_BADGES"
report "Duplicati: $EXACT_DUPLICATES"
report "Giorni sbilanciati: $UNBALANCED_DAYS"

log "AUDIT COMPLETATO: errori=${FAILURES}, avvisi=${WARNINGS}"