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

# STEP007 - SERVER ATTESO: 215.6 / ax-accessi
# Individua le righe sorgente esatte per:
# - ax_bands ID 3 e ID 1
# - ax_turn_adjust ID 0000
# - un ciclo che utilizzi il turno ID 1
# Nessuna modifica a file applicativi o database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP007_2156_DISTRIBUITE_DEMO_TURN_MISSING_ROWS_SOURCE_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}"
MAX_DATABASES="${STEP_MAX_DATABASES:-100}"

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 destinazione: $TARGET_DB"
    echo
} >>"$REPORT"

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

if [[ ! "$MAX_DATABASES" =~ ^[0-9]+$ ]] || [[ "$MAX_DATABASES" -lt 1 ]]; then
    fail "STEP_MAX_DATABASES non valido: $MAX_DATABASES"
fi

[[ "$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';"
}

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

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

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

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

log "[1/5] Stato attuale Distribuite"

"${MYSQL[@]}" -e "
SHOW CREATE TABLE \`${TARGET_DB}\`.ax_bands;
SHOW CREATE TABLE \`${TARGET_DB}\`.ax_turn_adjust;
SHOW CREATE TABLE \`${TARGET_DB}\`.ax_turns_cycle;" \
    >"${WORKDIR}/01_TARGET_TABLE_DEFINITIONS.tsv" 2>>"$LOGFILE"

"${MYSQL[@]}" -e "
SELECT * FROM \`${TARGET_DB}\`.ax_bands ORDER BY bnd_id;
SELECT * FROM \`${TARGET_DB}\`.ax_turn_adjust ORDER BY adj_id;
SELECT * FROM \`${TARGET_DB}\`.ax_turns_cycle ORDER BY trn_cycle_id;
SELECT * FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=1;" \
    >"${WORKDIR}/02_TARGET_CURRENT_ROWS.tsv" 2>>"$LOGFILE"

TARGET_BAND_1="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_bands WHERE bnd_id=1;")"
TARGET_BAND_3="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_bands WHERE bnd_id=3;")"
TARGET_ADJUST_0000="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turn_adjust WHERE adj_id='0000';")"
TARGET_CYCLE_1="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_cycle WHERE trn_cycle_id=1;")"
TARGET_TURN_1="$(sql_scalar "SELECT COUNT(*) FROM \`${TARGET_DB}\`.ax_turns_new WHERE trn_id=1;")"

{
    printf 'voce\tconteggio\n'
    printf 'target_band_1\t%s\n' "$TARGET_BAND_1"
    printf 'target_band_3\t%s\n' "$TARGET_BAND_3"
    printf 'target_adjust_0000\t%s\n' "$TARGET_ADJUST_0000"
    printf 'target_cycle_1\t%s\n' "$TARGET_CYCLE_1"
    printf 'target_turn_1\t%s\n' "$TARGET_TURN_1"
} >"${WORKDIR}/03_TARGET_COUNTS.tsv"

log "[2/5] Elenco database candidati"

DB_LIST="${WORKDIR}/04_DATABASES.txt"

"${MYSQL[@]}" --skip-column-names -e "
SELECT DISTINCT TABLE_SCHEMA
FROM information_schema.TABLES
WHERE TABLE_NAME IN ('ax_bands','ax_turn_adjust','ax_turns_cycle','ax_turns_new')
  AND TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys','${TARGET_DB}')
ORDER BY
  CASE
    WHEN TABLE_SCHEMA='presenze_dvmservice' THEN 0
    WHEN TABLE_SCHEMA='presenze_silvelox' THEN 1
    ELSE 2
  END,
  TABLE_SCHEMA
LIMIT ${MAX_DATABASES};" >"$DB_LIST" 2>>"$LOGFILE"

DB_COUNT="$(grep -cve '^[[:space:]]*$' "$DB_LIST" || true)"
report "Database candidati: $DB_COUNT"

[[ "$DB_COUNT" -gt 0 ]] || {
    fail "nessun database sorgente candidato trovato"
    exit 25
}

log "[3/5] Ricerca fasce e arrotondamenti mancanti"

BANDS_SUMMARY="${WORKDIR}/05_BANDS_SOURCE_SUMMARY.tsv"
ADJUST_SUMMARY="${WORKDIR}/06_ADJUST_SOURCE_SUMMARY.tsv"
CYCLES_SUMMARY="${WORKDIR}/07_CYCLES_SOURCE_SUMMARY.tsv"

printf 'database\tband_1\tband_3\n' >"$BANDS_SUMMARY"
printf 'database\tadjust_0000\n' >"$ADJUST_SUMMARY"
printf 'database\tcycle_rows\tcycle_id_1\tcycles_using_turn_1\n' >"$CYCLES_SUMMARY"

INDEX=0
FOUND_BAND_3=0
FOUND_ADJUST_0000=0
FOUND_CYCLE_TURN_1=0

while IFS= read -r db; do
    [[ -n "$db" ]] || continue

    INDEX=$((INDEX + 1))
    SAFE_DB="$(printf '%s' "$db" | tr -c '[:alnum:]_-' '_')"

    log "[${INDEX}/${DB_COUNT}] $db"

    band_1=0
    band_3=0
    adjust_0000=0
    cycle_rows=0
    cycle_id_1=0
    cycles_using_turn_1=0

    if [[ "$(table_exists "$db" ax_bands)" == "1" ]]; then
        band_1="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_bands WHERE bnd_id=1;")"
        band_3="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_bands WHERE bnd_id=3;")"

        "${MYSQL[@]}" -e "
SELECT *
FROM \`${db}\`.ax_bands
WHERE bnd_id IN (1,3)
ORDER BY bnd_id;" >"${WORKDIR}/20_${SAFE_DB}_BANDS_1_3.tsv" 2>>"$LOGFILE" || true
    fi

    if [[ "$(table_exists "$db" ax_turn_adjust)" == "1" ]]; then
        adjust_0000="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turn_adjust WHERE adj_id='0000';")"

        "${MYSQL[@]}" -e "
SELECT *
FROM \`${db}\`.ax_turn_adjust
WHERE adj_id='0000';" >"${WORKDIR}/21_${SAFE_DB}_ADJUST_0000.tsv" 2>>"$LOGFILE" || true
    fi

    if [[ "$(table_exists "$db" ax_turns_cycle)" == "1" ]]; then
        cycle_rows="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turns_cycle;")"
        cycle_id_1="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turns_cycle WHERE trn_cycle_id=1;")"

        if [[ "$(column_exists "$db" ax_turns_cycle trn_cycle_repeat)" == "1" ]]; then
            cycles_using_turn_1="$(sql_scalar "
SELECT COUNT(*)
FROM \`${db}\`.ax_turns_cycle
WHERE trn_cycle_repeat REGEXP '\"turn\"[[:space:]]*:[[:space:]]*\"?1\"?';")"

            "${MYSQL[@]}" -e "
SELECT *
FROM \`${db}\`.ax_turns_cycle
WHERE trn_cycle_id=1
   OR trn_cycle_repeat REGEXP '\"turn\"[[:space:]]*:[[:space:]]*\"?1\"?'
ORDER BY trn_cycle_id;" >"${WORKDIR}/22_${SAFE_DB}_CYCLES_TURN_1.tsv" 2>>"$LOGFILE" || true
        fi
    fi

    printf '%s\t%s\t%s\n' "$db" "$band_1" "$band_3" >>"$BANDS_SUMMARY"
    printf '%s\t%s\n' "$db" "$adjust_0000" >>"$ADJUST_SUMMARY"
    printf '%s\t%s\t%s\t%s\n' \
        "$db" "$cycle_rows" "$cycle_id_1" "$cycles_using_turn_1" >>"$CYCLES_SUMMARY"

    [[ "$band_3" -gt 0 ]] && FOUND_BAND_3=$((FOUND_BAND_3 + 1))
    [[ "$adjust_0000" -gt 0 ]] && FOUND_ADJUST_0000=$((FOUND_ADJUST_0000 + 1))
    [[ "$cycles_using_turn_1" -gt 0 ]] && FOUND_CYCLE_TURN_1=$((FOUND_CYCLE_TURN_1 + 1))
done <"$DB_LIST"

log "[4/5] Valutazione piano"

PLAN=""

if [[ "$TARGET_TURN_1" != "1" ]]; then
    fail "turno demo 1 assente o duplicato nel target"
elif [[ "$TARGET_BAND_1" == "1" && "$TARGET_BAND_3" == "0" ]]; then
    PLAN="NORMALIZZARE_TURNO_SU_BAND_1"
else
    PLAN="VERIFICA_MANUALE_FASCE"
fi

if [[ "$TARGET_ADJUST_0000" == "0" && "$FOUND_ADJUST_0000" -gt 0 ]]; then
    PLAN="${PLAN}+COPIARE_ADJUST_0000"
elif [[ "$TARGET_ADJUST_0000" == "0" ]]; then
    warn "nessuna sorgente contiene ax_turn_adjust adj_id=0000"
    PLAN="${PLAN}+VALUTARE_SENTINEL_ADJUST"
fi

if [[ "$TARGET_CYCLE_1" == "0" && "$FOUND_CYCLE_TURN_1" -gt 0 ]]; then
    PLAN="${PLAN}+CREARE_CICLO_DEMO_DA_SORGENTE"
elif [[ "$TARGET_CYCLE_1" == "0" ]]; then
    warn "nessun ciclo sorgente che richiami il turno 1"
    PLAN="${PLAN}+CREARE_CICLO_DEMO_SINTETICO"
fi

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Sorgenti con fascia 3: $FOUND_BAND_3"
    echo "Sorgenti con adjust 0000: $FOUND_ADJUST_0000"
    echo "Sorgenti con ciclo che usa turno 1: $FOUND_CYCLE_TURN_1"
    echo "Piano: $PLAN"
    echo "Nessuna modifica eseguita."
} >"${WORKDIR}/99_SUMMARY.txt"

report "Sorgenti con fascia 3: $FOUND_BAND_3"
report "Sorgenti con adjust 0000: $FOUND_ADJUST_0000"
report "Sorgenti con ciclo che usa turno 1: $FOUND_CYCLE_TURN_1"
report "Piano: $PLAN"

log "[5/5] Audit completato: errori=${FAILURES}, avvisi=${WARNINGS}"