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

# STEP008B - SERVER ATTESO: 215.6 / ax-accessi
# Audit corretto delle dipendenze del turno demo:
# - bands=3 viene confrontato con la fascia demo 1
# - adjust=0000 viene cercato nella colonna adj_code, non nella PK adj_id
# - viene verificato il modello ciclo Silvelox 2026 da normalizzare sul turno 1
# Nessuna modifica a file o database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP008B_2156_DISTRIBUITE_DEMO_TURN_CORRECT_DEPENDENCIES_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}"
SOURCE_DB="${STEP_SOURCE_DB:-presenze_silvelox}"
TURN_ID="${STEP_TURN_ID:-1}"
TARGET_BAND_ID="${STEP_TARGET_BAND_ID:-1}"
SOURCE_BAND_ID="${STEP_SOURCE_BAND_ID:-3}"
SOURCE_CYCLE_ID="${STEP_SOURCE_CYCLE_ID:-15}"

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 "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: $TARGET_DB"
    echo "Database sorgente: $SOURCE_DB"
    echo "Turno target: $TURN_ID"
    echo "Fascia target: $TARGET_BAND_ID"
    echo "Fascia sorgente confronto: $SOURCE_BAND_ID"
    echo "Ciclo sorgente Silvelox: $SOURCE_CYCLE_ID"
    echo
} >>"$REPORT"

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

for value in "$TURN_ID" "$TARGET_BAND_ID" "$SOURCE_BAND_ID" "$SOURCE_CYCLE_ID"; do
    [[ "$value" =~ ^[0-9]+$ ]] || fail "ID non numerico: $value"
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)

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

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

for db in "$TARGET_DB" "$SOURCE_DB"; do
    [[ "$(sql_scalar "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='${db}';")" == "1" ]] \
        || fail "database assente: $db"
done

for table in ax_turns_new ax_turns_cycle ax_bands ax_turn_adjust; do
    [[ "$(table_exists "$TARGET_DB" "$table")" == "1" ]] \
        || fail "tabella target assente: ${TARGET_DB}.${table}"
    [[ "$(table_exists "$SOURCE_DB" "$table")" == "1" ]] \
        || fail "tabella sorgente assente: ${SOURCE_DB}.${table}"
done

[[ "$(column_exists "$TARGET_DB" ax_turn_adjust adj_code)" == "1" ]] \
    || fail "colonna ${TARGET_DB}.ax_turn_adjust.adj_code assente"

[[ "$(column_exists "$SOURCE_DB" ax_turn_adjust adj_code)" == "1" ]] \
    || fail "colonna ${SOURCE_DB}.ax_turn_adjust.adj_code assente"

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

log "[1/5] Stato turno e dipendenze target"

"${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TURN_ID};

SELECT *
FROM \`${TARGET_DB}\`.ax_bands
WHERE bnd_id IN (${TARGET_BAND_ID},${SOURCE_BAND_ID})
ORDER BY bnd_id;

SELECT *
FROM \`${TARGET_DB}\`.ax_turn_adjust
WHERE adj_code='0000'
ORDER BY adj_id;

SELECT *
FROM \`${TARGET_DB}\`.ax_turns_cycle
ORDER BY trn_cycle_id;" \
    >"${WORKDIR}/01_TARGET_ROWS.tsv" 2>>"$LOGFILE"

TARGET_TURN_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=${TURN_ID};")"
TARGET_BAND_1_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_bands WHERE bnd_id=${TARGET_BAND_ID};")"
TARGET_BAND_3_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_bands WHERE bnd_id=${SOURCE_BAND_ID};")"
TARGET_ADJUST_CODE_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turn_adjust WHERE adj_code='0000';")"
TARGET_CYCLE_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_cycle;")"

TURN_JSON="$(sql_scalar "SELECT trn_work_days FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=${TURN_ID};")"
TURN_BAND_3_REFS="$( { printf '%s' "$TURN_JSON" | grep -o '"bands":"3"' || true; } | wc -l | tr -d ' ')"
TURN_BAND_1_REFS="$( { printf '%s' "$TURN_JSON" | grep -o '"bands":"1"' || true; } | wc -l | tr -d ' ')"
TURN_ADJUST_0000_REFS="$( { printf '%s' "$TURN_JSON" | grep -o '"adjust":"0000"' || true; } | wc -l | tr -d ' ')"

{
    printf 'voce\tvalore\n'
    printf 'target_turn_count\t%s\n' "$TARGET_TURN_COUNT"
    printf 'target_band_1_count\t%s\n' "$TARGET_BAND_1_COUNT"
    printf 'target_band_3_count\t%s\n' "$TARGET_BAND_3_COUNT"
    printf 'target_adjust_code_0000_count\t%s\n' "$TARGET_ADJUST_CODE_COUNT"
    printf 'target_cycle_rows\t%s\n' "$TARGET_CYCLE_COUNT"
    printf 'turn_refs_band_3\t%s\n' "$TURN_BAND_3_REFS"
    printf 'turn_refs_band_1\t%s\n' "$TURN_BAND_1_REFS"
    printf 'turn_refs_adjust_0000\t%s\n' "$TURN_ADJUST_0000_REFS"
} >"${WORKDIR}/02_TARGET_COUNTS.tsv"

[[ "$TARGET_TURN_COUNT" == "1" ]] || fail "turno target ${TURN_ID} assente o duplicato"
[[ "$TARGET_BAND_1_COUNT" == "1" ]] || fail "fascia demo target ${TARGET_BAND_ID} assente o duplicata"
[[ "$TARGET_CYCLE_COUNT" == "0" ]] || warn "target contiene gia' ${TARGET_CYCLE_COUNT} cicli"

log "[2/5] Verifica adjust=0000 nella colonna corretta adj_code"

"${MYSQL[@]}" -e "
SELECT *
FROM \`${SOURCE_DB}\`.ax_turn_adjust
WHERE adj_code='0000'
ORDER BY adj_id;" >"${WORKDIR}/03_SOURCE_ADJUST_CODE_0000.tsv" 2>>"$LOGFILE"

SOURCE_ADJUST_CODE_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${SOURCE_DB}\`.ax_turn_adjust WHERE adj_code='0000';")"

report "Target adjust adj_code=0000: $TARGET_ADJUST_CODE_COUNT"
report "Silvelox adjust adj_code=0000: $SOURCE_ADJUST_CODE_COUNT"

if [[ "$TARGET_ADJUST_CODE_COUNT" == "0" && "$SOURCE_ADJUST_CODE_COUNT" == "0" ]]; then
    report "Interpretazione adjust=0000: valore sentinella senza riga anagrafica, coerente con Silvelox."
elif [[ "$TARGET_ADJUST_CODE_COUNT" == "0" && "$SOURCE_ADJUST_CODE_COUNT" -gt 0 ]]; then
    warn "la riga adjust adj_code=0000 manca nel target ma esiste in Silvelox"
fi

log "[3/5] Confronto fascia demo target e fascia 3 Silvelox"

"${MYSQL[@]}" -e "
SELECT *
FROM \`${SOURCE_DB}\`.ax_bands
WHERE bnd_id IN (${TARGET_BAND_ID},${SOURCE_BAND_ID})
ORDER BY bnd_id;" >"${WORKDIR}/04_SOURCE_BANDS_1_3.tsv" 2>>"$LOGFILE"

SOURCE_BAND_1_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${SOURCE_DB}\`.ax_bands WHERE bnd_id=${TARGET_BAND_ID};")"
SOURCE_BAND_3_COUNT="$(sql_scalar "SELECT COUNT(*) FROM \`${SOURCE_DB}\`.ax_bands WHERE bnd_id=${SOURCE_BAND_ID};")"

TARGET_BAND_1_HASH="$(sql_scalar "
SELECT SHA2(CONCAT_WS(CHAR(31),bnd_name,bnd_time,COALESCE(bnd_note,'')),256)
FROM \`${TARGET_DB}\`.ax_bands
WHERE bnd_id=${TARGET_BAND_ID};")"

SOURCE_BAND_1_HASH="$(sql_scalar "
SELECT SHA2(CONCAT_WS(CHAR(31),bnd_name,bnd_time,COALESCE(bnd_note,'')),256)
FROM \`${SOURCE_DB}\`.ax_bands
WHERE bnd_id=${TARGET_BAND_ID};")"

SOURCE_BAND_3_HASH="$(sql_scalar "
SELECT SHA2(CONCAT_WS(CHAR(31),bnd_name,bnd_time,COALESCE(bnd_note,'')),256)
FROM \`${SOURCE_DB}\`.ax_bands
WHERE bnd_id=${SOURCE_BAND_ID};")"

{
    printf 'voce\tvalore\n'
    printf 'source_band_1_count\t%s\n' "$SOURCE_BAND_1_COUNT"
    printf 'source_band_3_count\t%s\n' "$SOURCE_BAND_3_COUNT"
    printf 'target_band_1_hash\t%s\n' "$TARGET_BAND_1_HASH"
    printf 'source_band_1_hash\t%s\n' "$SOURCE_BAND_1_HASH"
    printf 'source_band_3_hash\t%s\n' "$SOURCE_BAND_3_HASH"
} >"${WORKDIR}/05_BAND_COMPARISON.tsv"

[[ "$SOURCE_BAND_3_COUNT" == "1" ]] || warn "fascia Silvelox ${SOURCE_BAND_ID} assente o duplicata"

log "[4/5] Verifica ciclo Silvelox 2026"

"${MYSQL[@]}" -e "
SELECT *
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};" \
    >"${WORKDIR}/06_SOURCE_CYCLE_2026.tsv" 2>>"$LOGFILE"

SOURCE_CYCLE_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};")"

SOURCE_CYCLE_NAME="$(sql_scalar "
SELECT trn_cycle_name
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};")"

SOURCE_CYCLE_REPEAT="$(sql_scalar "
SELECT trn_cycle_repeat
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};")"

SOURCE_CYCLE_MODEL="$(sql_scalar "
SELECT trn_cycle_model
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};")"

SOURCE_REPEAT_TURN_15="$( { printf '%s' "$SOURCE_CYCLE_REPEAT" | grep -o '"turn":"15"' || true; } | wc -l | tr -d ' ')"
SOURCE_REPEAT_OTHER_TURNS="$(printf '%s' "$SOURCE_CYCLE_REPEAT" \
    | grep -oE '"turn":"[0-9]+"' \
    | grep -vc '"turn":"15"' || true)"

SOURCE_MODEL_TURN_15="$( { printf '%s' "$SOURCE_CYCLE_MODEL" | grep -o '"turn":"15"' || true; } | wc -l | tr -d ' ')"
SOURCE_MODEL_OTHER_TURNS="$(printf '%s' "$SOURCE_CYCLE_MODEL" \
    | grep -oE '"turn":"[0-9]+"' \
    | grep -vc '"turn":"15"' || true)"

SOURCE_MODEL_2026_DATES="$( { printf '%s' "$SOURCE_CYCLE_MODEL" | grep -o '"from":"2026-' || true; } | wc -l | tr -d ' ')"
SOURCE_MODEL_OTHER_YEAR_DATES="$(printf '%s' "$SOURCE_CYCLE_MODEL" \
    | grep -oE '"from":"[0-9]{4}-' \
    | grep -vc '"from":"2026-' || true)"

{
    printf 'voce\tvalore\n'
    printf 'source_cycle_count\t%s\n' "$SOURCE_CYCLE_COUNT"
    printf 'source_cycle_name\t%s\n' "$SOURCE_CYCLE_NAME"
    printf 'repeat_turn_15_refs\t%s\n' "$SOURCE_REPEAT_TURN_15"
    printf 'repeat_other_turn_refs\t%s\n' "$SOURCE_REPEAT_OTHER_TURNS"
    printf 'model_turn_15_refs\t%s\n' "$SOURCE_MODEL_TURN_15"
    printf 'model_other_turn_refs\t%s\n' "$SOURCE_MODEL_OTHER_TURNS"
    printf 'model_2026_dates\t%s\n' "$SOURCE_MODEL_2026_DATES"
    printf 'model_other_year_dates\t%s\n' "$SOURCE_MODEL_OTHER_YEAR_DATES"
} >"${WORKDIR}/07_SOURCE_CYCLE_ANALYSIS.tsv"

[[ "$SOURCE_CYCLE_COUNT" == "1" ]] || fail "ciclo Silvelox ${SOURCE_CYCLE_ID} assente o duplicato"
[[ "$SOURCE_REPEAT_TURN_15" -gt 0 ]] || warn "repeat ciclo Silvelox non richiama il turno 15"
[[ "$SOURCE_REPEAT_OTHER_TURNS" == "0" ]] || warn "repeat ciclo Silvelox contiene turni diversi da 15"
[[ "$SOURCE_MODEL_TURN_15" -gt 0 ]] || warn "model ciclo Silvelox non richiama il turno 15"
[[ "$SOURCE_MODEL_OTHER_TURNS" == "0" ]] || warn "model ciclo Silvelox contiene turni diversi da 15"
[[ "$SOURCE_MODEL_2026_DATES" -ge 365 ]] || warn "model ciclo Silvelox non copre interamente il 2026"
[[ "$SOURCE_MODEL_OTHER_YEAR_DATES" == "0" ]] || warn "model ciclo Silvelox contiene anni diversi dal 2026"

log "[5/5] Piano conclusivo"

PLAN_PARTS=()

if [[ "$TURN_BAND_3_REFS" -gt 0 && "$TARGET_BAND_1_COUNT" == "1" ]]; then
    PLAN_PARTS+=("NORMALIZZARE_TURNO_BANDS_3_A_1")
fi

if [[ "$TARGET_ADJUST_CODE_COUNT" == "0" && "$SOURCE_ADJUST_CODE_COUNT" == "0" ]]; then
    PLAN_PARTS+=("MANTENERE_ADJUST_0000_COME_SENTINELLA")
elif [[ "$TARGET_ADJUST_CODE_COUNT" == "0" && "$SOURCE_ADJUST_CODE_COUNT" -gt 0 ]]; then
    PLAN_PARTS+=("COPIARE_ADJUST_CODE_0000_DA_SILVELOX")
else
    PLAN_PARTS+=("ADJUST_0000_GIA_PRESENTE")
fi

if [[ "$TARGET_CYCLE_COUNT" == "0" \
   && "$SOURCE_CYCLE_COUNT" == "1" \
   && "$SOURCE_MODEL_2026_DATES" -ge 365 \
   && "$SOURCE_MODEL_OTHER_YEAR_DATES" == "0" \
   && "$SOURCE_MODEL_OTHER_TURNS" == "0" ]]; then
    PLAN_PARTS+=("CREARE_CICLO_DEMO_DA_SILVELOX_15_NORMALIZZATO_A_TURNO_1")
fi

PLAN="$(IFS=+; printf '%s' "${PLAN_PARTS[*]}")"

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Target turn refs bands=3: $TURN_BAND_3_REFS"
    echo "Target turn refs adjust=0000: $TURN_ADJUST_0000_REFS"
    echo "Target adjust adj_code=0000: $TARGET_ADJUST_CODE_COUNT"
    echo "Silvelox adjust adj_code=0000: $SOURCE_ADJUST_CODE_COUNT"
    echo "Silvelox ciclo ${SOURCE_CYCLE_ID}, date 2026: $SOURCE_MODEL_2026_DATES"
    echo "Silvelox ciclo ${SOURCE_CYCLE_ID}, riferimenti turno 15: $SOURCE_MODEL_TURN_15"
    echo "Piano: $PLAN"
    echo "Nessuna modifica eseguita."
} >"${WORKDIR}/99_SUMMARY.txt"

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