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

# STEP003 - SERVER ATTESO: 215.6 / ax-accessi
# Audit rapido: licenza reale, HTTP locale e coerenza dati demo/turni.
# Nessuna modifica a file o database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP003_2156_DISTRIBUITE_DEMO_DATA_LICENSE_HTTP_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}"
PORTAL_PATH="${STEP_PORTAL_PATH:-/var/www/html/presenze.distribuite}"
PORTAL_HOST="${STEP_PORTAL_HOST:-presenze.distribuite.axuno.it}"
PORTAL_DB="${STEP_PORTAL_DB:-presenze_distribuite}"
SOURCE_DB="${STEP_SOURCE_DB:-presenze_dvmservice}"
MASTER_DB="${STEP_MASTER_DB:-ax-master-license}"
EXPECTED_LICENSE="${STEP_EXPECTED_LICENSE:-A9002}"
SKIP_HTTP="${STEP_SKIP_HTTP:-0}"

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
    if [[ "$PACKAGED" -eq 1 ]]; then return 0; fi
    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
        (cd "$WORKDIR" && zip -qr "$OUTZIP" .) || zip_rc=$?
        if [[ "$zip_rc" -eq 0 ]]; then
            zip -T "$OUTZIP" >> "$LOGFILE" 2>&1 || zip_rc=$?
        fi
    else
        zip_rc=127
    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 "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 "Modalita': AUDIT ONLY"
    echo "Data: $(date --iso-8601=seconds)"
    echo "Hostname: $ACTUAL_HOST"
    echo "IP: $ACTUAL_IPS"
    echo "Portale: $PORTAL_PATH"
    echo "Host web: $PORTAL_HOST"
    echo "Database demo: $PORTAL_DB"
    echo "Database modello: $SOURCE_DB"
    echo "Licenza attesa: $EXPECTED_LICENSE"
    echo
} >> "$REPORT"

for cmd in grep sed awk wc sort head zip hostname xargs; do
    if ! command -v "$cmd" >/dev/null 2>&1; then
        fail "comando richiesto non disponibile: $cmd"
    fi
done
if [[ "$FAILURES" -ne 0 ]]; then exit 20; fi

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

if [[ ! -d "$PORTAL_PATH" ]]; then
    fail "cartella portale assente: $PORTAL_PATH"
    exit 21
fi

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)
if ! "${MYSQL[@]}" --skip-column-names -e 'SELECT 1' >/dev/null 2>&1; then
    fail "accesso MySQL locale non riuscito"
    exit 23
fi

sql_scalar() {
    local query="$1"
    "${MYSQL[@]}" --skip-column-names -e "$query" 2>>"$LOGFILE"
}

schema_exists() {
    local db="$1"
    sql_scalar "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='${db}';"
}

table_exists() {
    local db="$1" table="$2"
    sql_scalar "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='${db}' AND TABLE_NAME='${table}';"
}

column_exists() {
    local db="$1" table="$2" column="$3"
    sql_scalar "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='${db}' AND TABLE_NAME='${table}' AND COLUMN_NAME='${column}';"
}

table_rows() {
    local db="$1" table="$2"
    if [[ "$(table_exists "$db" "$table")" == "1" ]]; then
        sql_scalar "SELECT COUNT(*) FROM \`${db}\`.\`${table}\`;"
    else
        printf 'MANCANTE\n'
    fi
}

for db in "$PORTAL_DB" "$SOURCE_DB" "$MASTER_DB"; do
    present="$(schema_exists "$db")"
    report "Database ${db} presente: ${present}"
    [[ "$present" == "1" ]] || fail "database assente: $db"
done
if [[ "$FAILURES" -ne 0 ]]; then exit 24; fi

GLOB_FILE="${PORTAL_PATH}/ax_common/a000_ax_glob.php"
if [[ -f "$GLOB_FILE" ]]; then
    active_line="$(grep -E '^[[:space:]]*define[[:space:]]*\(' "$GLOB_FILE" | grep -F 'AXLICCODE' | head -n 1 || true)"
    active_code="$(printf '%s\n' "$active_line" | grep -oE 'A[0-9]{4,}' | head -n 1 || true)"
    printf '%s\n' "$active_line" > "${WORKDIR}/01_AXLICCODE.txt"
    report "AXLICCODE rilevato: ${active_code:-NESSUNO}"
    [[ "$active_code" == "$EXPECTED_LICENSE" ]] || fail "AXLICCODE ${active_code:-NESSUNO}; atteso ${EXPECTED_LICENSE}"
else
    fail "file globale assente: $GLOB_FILE"
fi

if [[ "$(table_exists "$MASTER_DB" ax_license)" == "1" ]]; then
    "${MYSQL[@]}" -e "
SELECT lic_id,lic_code_firm,lic_serial,lic_name,lic_www,lic_data,lic_data_end,
       lic_stato,lic_service,lic_start,lic_message,lic_db_name
FROM \`${MASTER_DB}\`.ax_license
WHERE lic_code_firm='${EXPECTED_LICENSE}' OR lic_db_name='${PORTAL_DB}'
ORDER BY lic_id;" > "${WORKDIR}/02_LICENSE.txt" 2>>"$LOGFILE" || fail "lettura record licenza fallita"

    lic_count="$(sql_scalar "SELECT COUNT(*) FROM \`${MASTER_DB}\`.ax_license WHERE lic_code_firm='${EXPECTED_LICENSE}' AND lic_db_name='${PORTAL_DB}';")"
    report "Record licenza esatti ${EXPECTED_LICENSE}/${PORTAL_DB}: ${lic_count}"
    [[ "$lic_count" == "1" ]] || fail "record licenza esatti: ${lic_count}; atteso 1"
else
    fail "tabella ${MASTER_DB}.ax_license assente"
fi

KEY_TABLES=(
    ax_user ax_associazioni_temporali ax_firms ax_user_group ax_position
    ax_contract ax_turns_cycle ax_turns ax_bands accessi_clockings terminal_clockings
)

{
    echo -e "tabella\t${PORTAL_DB}\t${SOURCE_DB}"
    for table in "${KEY_TABLES[@]}"; do
        printf '%s\t%s\t%s\n' "$table" "$(table_rows "$PORTAL_DB" "$table")" "$(table_rows "$SOURCE_DB" "$table")"
    done
} > "${WORKDIR}/03_KEY_TABLE_COUNTS.tsv"

for db in "$PORTAL_DB" "$SOURCE_DB"; do
    "${MYSQL[@]}" --skip-column-names -e "
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='${db}'
  AND (TABLE_NAME LIKE '%turn%' OR TABLE_NAME LIKE '%band%' OR TABLE_NAME LIKE '%cycle%')
ORDER BY TABLE_NAME;" > "${WORKDIR}/04_${db}_TURN_TABLES.txt" 2>>"$LOGFILE" || true

done

ASSOC_TABLE="ax_associazioni_temporali"
TURN_TABLE="ax_turns_cycle"
if [[ "$(table_exists "$PORTAL_DB" "$ASSOC_TABLE")" == "1" ]] && \
   [[ "$(column_exists "$PORTAL_DB" "$ASSOC_TABLE" usrTurns)" == "1" ]]; then
    "${MYSQL[@]}" -e "
SELECT usrTurns,COUNT(*) AS associazioni
FROM \`${PORTAL_DB}\`.\`${ASSOC_TABLE}\`
GROUP BY usrTurns
ORDER BY CAST(usrTurns AS UNSIGNED),usrTurns;" > "${WORKDIR}/05_DISTRIBUITE_USRTURNS.tsv" 2>>"$LOGFILE" || fail "lettura usrTurns fallita"

    turn_rows="$(table_rows "$PORTAL_DB" "$TURN_TABLE")"
    report "Righe ${PORTAL_DB}.${TURN_TABLE}: ${turn_rows}"
    if [[ "$turn_rows" == "0" ]]; then
        warn "${PORTAL_DB}.${TURN_TABLE} e' vuota: i dati demo dei turni non sono completi"
    fi

    if [[ "$(table_exists "$PORTAL_DB" "$TURN_TABLE")" == "1" ]] && \
       [[ "$(column_exists "$PORTAL_DB" "$TURN_TABLE" trn_cycle_id)" == "1" ]]; then
        orphan_turns="$(sql_scalar "
SELECT COUNT(*)
FROM \`${PORTAL_DB}\`.\`${ASSOC_TABLE}\` a
LEFT JOIN \`${PORTAL_DB}\`.\`${TURN_TABLE}\` t
  ON CAST(t.trn_cycle_id AS CHAR)=CAST(a.usrTurns AS CHAR)
WHERE COALESCE(CAST(a.usrTurns AS CHAR),'') NOT IN ('','0')
  AND t.trn_cycle_id IS NULL;")"
        report "Associazioni con usrTurns senza ciclo esistente: ${orphan_turns}"
        if [[ "$orphan_turns" != "0" ]]; then
            warn "trovate ${orphan_turns} associazioni con usrTurns orfano"
        fi
    fi
fi

for db in "$PORTAL_DB" "$SOURCE_DB"; do
    if [[ "$(table_exists "$db" "$TURN_TABLE")" == "1" ]]; then
        "${MYSQL[@]}" -e "SHOW CREATE TABLE \`${db}\`.\`${TURN_TABLE}\`;" > "${WORKDIR}/06_${db}_${TURN_TABLE}_CREATE.tsv" 2>>"$LOGFILE" || true
        "${MYSQL[@]}" -e "SELECT * FROM \`${db}\`.\`${TURN_TABLE}\` ORDER BY 1 LIMIT 200;" > "${WORKDIR}/07_${db}_${TURN_TABLE}_ROWS.tsv" 2>>"$LOGFILE" || true
    fi
done

for table in ax_turns ax_bands; do
    for db in "$PORTAL_DB" "$SOURCE_DB"; do
        if [[ "$(table_exists "$db" "$table")" == "1" ]]; then
            "${MYSQL[@]}" -e "SELECT * FROM \`${db}\`.\`${table}\` ORDER BY 1 LIMIT 200;" > "${WORKDIR}/08_${db}_${table}_ROWS.tsv" 2>>"$LOGFILE" || true
        fi
    done
done

if [[ "$SKIP_HTTP" != "1" ]]; then
    if command -v curl >/dev/null 2>&1; then
        HTTP_HEADERS="${WORKDIR}/09_HTTP_HEADERS.txt"
        HTTP_BODY="${WORKDIR}/09_HTTP_BODY_HEAD.txt"
        http_code="$(curl -sS --max-time 20 \
            -D "$HTTP_HEADERS" \
            -o "${WORKDIR}/http_body.tmp" \
            -H "Host: ${PORTAL_HOST}" \
            -H 'X-Forwarded-Proto: https' \
            -w '%{http_code}' \
            'http://127.0.0.1/' 2>>"$LOGFILE" || printf '000')"
        head -c 16384 "${WORKDIR}/http_body.tmp" > "$HTTP_BODY" 2>/dev/null || true
        rm -f "${WORKDIR}/http_body.tmp"
        report "HTTP locale con Host/X-Forwarded-Proto: ${http_code}"
        case "$http_code" in
            200|301|302|303|307|308) ;;
            *) fail "risposta HTTP locale inattesa: ${http_code}" ;;
        esac
        if grep -Eqi 'Fatal error|Parse error|Uncaught (Error|Exception)|Access denied for user' "$HTTP_BODY"; then
            fail "errore PHP/DB rilevato nel corpo HTTP; vedere 09_HTTP_BODY_HEAD.txt"
        fi
    else
        warn "curl non disponibile: controllo HTTP saltato"
    fi
fi

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Nessuna modifica eseguita."
} > "${WORKDIR}/99_SUMMARY.txt"

log "Audit completato: errori=${FAILURES}, avvisi=${WARNINGS}"
if [[ "$FAILURES" -ne 0 ]]; then exit 30; fi
exit 0