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

# STEP012 - SERVER ATTESO: 215.6 / ax-accessi
# Analizza lo schema reale di terminal_clockings e i badge demo disponibili
# nel database presenze_distribuite. Nessuna modifica al database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP012_2156_DISTRIBUITE_TERMINAL_CLOCKINGS_DEMO_SCOPE_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; 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}';")"

[[ "$DB_EXISTS" == "1" ]] || fail "database assente: $TARGET_DB"

for table in terminal_clockings ax_associazioni_temporali ax_user; do
    EXISTS="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='${table}';")"

    [[ "$EXISTS" == "1" ]] || fail "tabella assente: ${TARGET_DB}.${table}"
done

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

log "[1/5] Struttura terminal_clockings"

"${MYSQL[@]}" -e "
SHOW CREATE TABLE \`${TARGET_DB}\`.terminal_clockings;

SELECT
    ORDINAL_POSITION,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_KEY,
    EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
ORDER BY ORDINAL_POSITION;

SELECT
    INDEX_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX,
    COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
ORDER BY INDEX_NAME,SEQ_IN_INDEX;" \
    >"${WORKDIR}/01_TERMINAL_CLOCKINGS_SCHEMA.tsv" 2>>"$LOGFILE"

log "[2/5] Stato attuale terminal_clockings"

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

"${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.terminal_clockings
ORDER BY 1 DESC
LIMIT 20;" >"${WORKDIR}/02_TERMINAL_CLOCKINGS_SAMPLE.tsv" 2>>"$LOGFILE"

{
    printf 'voce\tvalore\n'
    printf 'terminal_clockings_rows\t%s\n' "$ROW_COUNT"
} >"${WORKDIR}/03_COUNTS.tsv"

log "[3/5] Badge demo attivi"

"${MYSQL[@]}" -e "
SELECT
    a.tbaaId,
    a.usrId,
    a.usrBdg,
    a.usrTurns,
    a.\`from\`,
    a.\`to\`,
    a.attuale,
    u.usrFirst,
    u.usrLast,
    u.usrActive
FROM \`${TARGET_DB}\`.ax_associazioni_temporali a
LEFT JOIN \`${TARGET_DB}\`.ax_user u
       ON u.usrId=a.usrId
WHERE a.attuale='1'
  AND u.usrActive='1'
  AND a.usrBdg IS NOT NULL
  AND TRIM(a.usrBdg)<>''
ORDER BY a.usrId
LIMIT 100;" >"${WORKDIR}/04_ACTIVE_DEMO_BADGES.tsv" 2>>"$LOGFILE"

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

report "Righe terminal_clockings attuali: $ROW_COUNT"
report "Badge demo attivi disponibili: $ACTIVE_BADGES"

[[ "$ACTIVE_BADGES" -gt 0 ]] || fail "nessun badge demo attivo disponibile"

log "[4/5] Colonne compatibili con le timbrature"

"${MYSQL[@]}" -e "
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
  AND COLUMN_NAME IN (
      'id',
      'BadgeNumber',
      'DateAndTime',
      'TerminalId',
      'originalData',
      'Type',
      'Direction',
      'AccessType'
  )
ORDER BY ORDINAL_POSITION;" >"${WORKDIR}/05_EXPECTED_COLUMNS.tsv" 2>>"$LOGFILE"

BADGE_COL="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
  AND COLUMN_NAME='BadgeNumber';")"

DATE_COL="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
  AND COLUMN_NAME='DateAndTime';")"

TERMINAL_COL="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
  AND COLUMN_NAME='TerminalId';")"

[[ "$BADGE_COL" == "1" ]] || fail "colonna BadgeNumber assente"
[[ "$DATE_COL" == "1" ]] || fail "colonna DateAndTime assente"
[[ "$TERMINAL_COL" == "1" ]] || fail "colonna TerminalId assente"

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

log "[5/5] Piano inserimento demo"

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Righe terminal_clockings attuali: $ROW_COUNT"
    echo "Badge demo disponibili: $ACTIVE_BADGES"
    echo "Schema completo acquisito."
    echo "Prossimo passo: generazione timbrature demo coerenti con lo schema reale."
    echo "Nessuna modifica eseguita."
} >"${WORKDIR}/99_SUMMARY.txt"

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