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

# STEP006 - SERVER ATTESO: 215.6 / ax-accessi
# Verifica il turno demo gia' presente in presenze_distribuite e stabilisce
# se manca soltanto il relativo ciclo. Nessuna modifica a file o database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP006_2156_DISTRIBUITE_DEMO_TURN_CYCLE_PLAN_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}"
TURN_ID="${STEP_TURN_ID:-1}"
CYCLE_ID="${STEP_CYCLE_ID:-1}"

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
        printf '%s\n' "ERRORE: comando zip non disponibile" >&2
        rm -rf -- "$WORKDIR"
        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 "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 "Database: $TARGET_DB"
    echo "Turno demo: $TURN_ID"
    echo "Ciclo atteso: $CYCLE_ID"
    echo
} >>"$REPORT"

for cmd in grep sed awk wc sort zip hostname xargs tr cut; do
    command -v "$cmd" >/dev/null 2>&1 || fail "comando richiesto non disponibile: $cmd"
done
[[ "$FAILURES" -eq 0 ]] || exit 20

if [[ ! "$TURN_ID" =~ ^[0-9]+$ || ! "$CYCLE_ID" =~ ^[0-9]+$ ]]; then
    fail "ID ciclo/turno non numerico"
    exit 21
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
[[ "$FAILURES" -eq 0 ]] || exit 22

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 23
    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 24
fi

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

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

primary_key() {
    sql_scalar "
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='$1'
  AND TABLE_NAME='$2'
  AND COLUMN_KEY='PRI'
ORDER BY ORDINAL_POSITION
LIMIT 1;"
}

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 ax_turns_new ax_turns_cycle ax_associazioni_temporali; do
    [[ "$(table_exists "$TARGET_DB" "$table")" == "1" ]] || fail "tabella assente: ${TARGET_DB}.${table}"
done
[[ "$FAILURES" -eq 0 ]] || exit 25

TURN_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=${TURN_ID};")"
CYCLE_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_cycle WHERE trn_cycle_id=${CYCLE_ID};")"
ASSOC_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_associazioni_temporali WHERE usrTurns='${TURN_ID}';")"
CURRENT_ASSOC_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_associazioni_temporali WHERE usrTurns='${TURN_ID}' AND attuale='1';")"

{
    printf 'voce\tconteggio\n'
    printf 'turno_%s\t%s\n' "$TURN_ID" "$TURN_COUNT"
    printf 'ciclo_%s\t%s\n' "$CYCLE_ID" "$CYCLE_COUNT"
    printf 'associazioni_usrTurns_%s\t%s\n' "$TURN_ID" "$ASSOC_COUNT"
    printf 'associazioni_attuali_usrTurns_%s\t%s\n' "$TURN_ID" "$CURRENT_ASSOC_COUNT"
} >"${WORKDIR}/01_COUNTS.tsv"

report "Turno ${TURN_ID}: ${TURN_COUNT}"
report "Ciclo ${CYCLE_ID}: ${CYCLE_COUNT}"
report "Associazioni con usrTurns=${TURN_ID}: ${ASSOC_COUNT}"
report "Associazioni attuali con usrTurns=${TURN_ID}: ${CURRENT_ASSOC_COUNT}"

[[ "$TURN_COUNT" == "1" ]] || fail "turno demo ${TURN_ID} assente o duplicato"
[[ "$CYCLE_COUNT" -le 1 ]] || fail "ciclo ${CYCLE_ID} duplicato"
[[ "$FAILURES" -eq 0 ]] || exit 26

"${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TURN_ID};" >"${WORKDIR}/02_TARGET_TURN.tsv" 2>>"$LOGFILE"

"${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.ax_turns_cycle
ORDER BY trn_cycle_id;" >"${WORKDIR}/03_EXISTING_CYCLES.tsv" 2>>"$LOGFILE"

"${MYSQL[@]}" -e "
SELECT tbaaId,usrId,usrBdg,usrTurns,\`from\`,\`to\`,attuale
FROM \`${TARGET_DB}\`.ax_associazioni_temporali
WHERE usrTurns='${TURN_ID}'
ORDER BY attuale DESC,usrId,tbaaId;" >"${WORKDIR}/04_ASSOCIATIONS_USING_TURN.tsv" 2>>"$LOGFILE"

"${MYSQL[@]}" -e "
SHOW CREATE TABLE \`${TARGET_DB}\`.ax_turns_new;
SHOW CREATE TABLE \`${TARGET_DB}\`.ax_turns_cycle;" >"${WORKDIR}/05_TABLE_DEFINITIONS.tsv" 2>>"$LOGFILE"

WORK_DAYS="$(sql_scalar "SELECT trn_work_days FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=${TURN_ID};")"
printf '%s\n' "$WORK_DAYS" >"${WORKDIR}/06_TARGET_TRN_WORK_DAYS.json"

printf 'chiave\tvalori_turno_demo\n' >"${WORKDIR}/07_REFERENCES.tsv"
for key in model bands adjust access constraints service magg; do
    values="$(printf '%s\n' "$WORK_DAYS" \
        | grep -oE "\"${key}\"[[:space:]]*:[[:space:]]*\"?[0-9]+\"?" \
        | grep -oE '[0-9]+' \
        | sort -n -u \
        | tr '\n' ',' \
        | sed 's/,$//' || true)"
    printf '%s\t%s\n' "$key" "$values" >>"${WORKDIR}/07_REFERENCES.tsv"
done

get_ref_values() {
    awk -F'\t' -v k="$1" '$1==k {print $2}' "${WORKDIR}/07_REFERENCES.tsv"
}

check_reference() {
    local label="$1"
    local table="$2"
    local wanted="$3"
    local result_file="${WORKDIR}/REF_${label}.tsv"

    printf 'tabella\tpk\tid_richiesti\trighe_presenti\n' >"$result_file"

    if [[ -z "$wanted" ]]; then
        printf '%s\t\t\t0\n' "$table" >>"$result_file"
        return 0
    fi

    if [[ "$(table_exists "$TARGET_DB" "$table")" != "1" ]]; then
        printf '%s\tASSENTE\t%s\t0\n' "$table" "$wanted" >>"$result_file"
        warn "tabella dipendenza assente: ${TARGET_DB}.${table}"
        return 0
    fi

    local pk
    pk="$(primary_key "$TARGET_DB" "$table")"
    if [[ -z "$pk" ]]; then
        printf '%s\tNESSUNA_PK\t%s\t0\n' "$table" "$wanted" >>"$result_file"
        warn "chiave primaria non rilevata: ${TARGET_DB}.${table}"
        return 0
    fi

    local ids_sql
    ids_sql="$(printf '%s' "$wanted" | awk -F, '{
        for (i=1;i<=NF;i++) {
            if ($i ~ /^[0-9]+$/) {
                if (out!="") out=out",";
                out=out sprintf("%d",$i)
            }
        }
        print out
    }')"

    if [[ -z "$ids_sql" ]]; then
        printf '%s\t%s\t%s\t0\n' "$table" "$pk" "$wanted" >>"$result_file"
        return 0
    fi

    local found requested
    found="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.\`${table}\` WHERE \`${pk}\` IN (${ids_sql});")"
    requested="$(printf '%s' "$ids_sql" | awk -F, '{print NF}')"

    printf '%s\t%s\t%s\t%s\n' "$table" "$pk" "$wanted" "$found" >>"$result_file"

    "${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.\`${table}\`
WHERE \`${pk}\` IN (${ids_sql})
ORDER BY \`${pk}\`;" >>"$result_file" 2>>"$LOGFILE" || true

    if [[ "$found" -lt "$requested" ]]; then
        warn "${table}: presenti ${found}/${requested} riferimenti richiesti (${wanted})"
    fi
}

check_reference "MODEL"       "ax_turn_model"           "$(get_ref_values model)"
check_reference "BANDS"       "ax_bands"                "$(get_ref_values bands)"
check_reference "ADJUST"      "ax_turn_adjust"          "$(get_ref_values adjust)"
check_reference "CONSTRAINTS" "ax_turn_constraints_new" "$(get_ref_values constraints)"
check_reference "SERVICE"     "ax_service"              "$(get_ref_values service)"
check_reference "ACCESS"      "ax_turn_tipo_accesso"    "$(get_ref_values access)"

if [[ "$CYCLE_COUNT" == "0" && "$WARNINGS" -eq 0 ]]; then
    report "PIANO: il turno demo e le sue dipendenze sono completi; manca soltanto il ciclo ${CYCLE_ID}."
    PLAN="INSERT_SOLO_CICLO"
elif [[ "$CYCLE_COUNT" == "1" && "$WARNINGS" -eq 0 ]]; then
    report "PIANO: turno demo e ciclo risultano gia' completi; nessun INSERT necessario."
    PLAN="NESSUNA_MODIFICA"
else
    report "PIANO: dipendenze incomplete; non inserire ancora il ciclo."
    PLAN="BLOCCATO_DIPENDENZE"
fi

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Database: $TARGET_DB"
    echo "Turno demo: $TURN_ID"
    echo "Ciclo atteso: $CYCLE_ID"
    echo "Piano: $PLAN"
    echo "Nessuna modifica eseguita."
} >"${WORKDIR}/99_SUMMARY.txt"

log "Audit completato: errori=${FAILURES}, avvisi=${WARNINGS}, piano=${PLAN}"