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

# STEP004 - SERVER ATTESO: 215.6 / ax-accessi
# Individua nei database locali una sorgente coerente per il ciclo turno ID 1.
# Nessuna modifica a file o database.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP004_2156_DISTRIBUITE_TURN_CYCLE_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}"
PORTAL_DB="${STEP_PORTAL_DB:-presenze_distribuite}"
CYCLE_ID="${STEP_CYCLE_ID:-1}"
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
    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 "Database destinazione: $PORTAL_DB"
    echo "Ciclo da ricostruire: $CYCLE_ID"
    echo
} >> "$REPORT"

for cmd in grep sed awk wc sort head zip hostname xargs tr cut; 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 [[ ! "$CYCLE_ID" =~ ^[0-9]+$ ]]; then
    fail "STEP_CYCLE_ID non numerico: $CYCLE_ID"
    exit 21
fi
if [[ ! "$MAX_DATABASES" =~ ^[0-9]+$ ]] || [[ "$MAX_DATABASES" -lt 1 ]]; then
    fail "STEP_MAX_DATABASES non valido: $MAX_DATABASES"
    exit 22
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 [[ "$FAILURES" -ne 0 ]]; then exit 23; 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 24
    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 25
fi

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

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

DB_LIST="${WORKDIR}/01_DATABASES_WITH_AX_TURNS_CYCLE.txt"
"${MYSQL[@]}" --skip-column-names -e "
SELECT DISTINCT TABLE_SCHEMA
FROM information_schema.TABLES
WHERE TABLE_NAME='ax_turns_cycle'
  AND TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
ORDER BY
  CASE
    WHEN TABLE_SCHEMA='presenze_silvelox' THEN 0
    WHEN TABLE_SCHEMA='presenze_distribuite' THEN 1
    WHEN TABLE_SCHEMA='presenze_dvmservice' THEN 2
    ELSE 3
  END,
  TABLE_SCHEMA
LIMIT ${MAX_DATABASES};" > "$DB_LIST" 2>>"$LOGFILE" || {
    fail "lettura database con ax_turns_cycle fallita"
    exit 26
}

DB_COUNT="$(grep -cve '^[[:space:]]*$' "$DB_LIST" || true)"
report "Database con ax_turns_cycle trovati: $DB_COUNT"
if [[ "$DB_COUNT" -eq 0 ]]; then
    fail "nessun database locale contiene ax_turns_cycle"
    exit 27
fi

SUMMARY_TSV="${WORKDIR}/02_CYCLE_ID_${CYCLE_ID}_SUMMARY.tsv"
printf 'database\trighe_cicli\tpresente_id_%s\tnome_ciclo\trepeat\tturn_ids\ttabella_turni\tpk_turni\trighe_turni_riferite\n' "$CYCLE_ID" > "$SUMMARY_TSV"

CANDIDATES=0
INDEX=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}] Analisi database: $db"

    cycle_count="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turns_cycle;")"
    id_count="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turns_cycle WHERE trn_cycle_id=${CYCLE_ID};")"
    cycle_name=""
    cycle_repeat=""
    turn_ids=""
    turn_table="ASSENTE"
    turn_pk=""
    referred_rows="0"

    "${MYSQL[@]}" -e "
SELECT trn_cycle_id,trn_cycle_name,trn_cycle_repeat,trn_cycle_enable,trn_cycle_note,trn_cycle_typ
FROM \`${db}\`.ax_turns_cycle
ORDER BY trn_cycle_id;" > "${WORKDIR}/10_${SAFE_DB}_ALL_CYCLES.tsv" 2>>"$LOGFILE" || true

    if [[ "$id_count" == "1" ]]; then
        CANDIDATES=$((CANDIDATES + 1))
        cycle_name="$(sql_scalar "SELECT trn_cycle_name FROM \`${db}\`.ax_turns_cycle WHERE trn_cycle_id=${CYCLE_ID} LIMIT 1;")"
        cycle_repeat="$(sql_scalar "SELECT trn_cycle_repeat FROM \`${db}\`.ax_turns_cycle WHERE trn_cycle_id=${CYCLE_ID} LIMIT 1;")"
        printf '%s\n' "$cycle_repeat" > "${WORKDIR}/11_${SAFE_DB}_CYCLE_${CYCLE_ID}_REPEAT.json"

        turn_ids="$(printf '%s\n' "$cycle_repeat" \
            | grep -oE '"turn"[[:space:]]*:[[:space:]]*"?[0-9]+' \
            | grep -oE '[0-9]+$' \
            | sort -n -u \
            | tr '\n' ',' \
            | sed 's/,$//' || true)"

        if [[ "$(table_exists "$db" ax_turns_new)" == "1" ]]; then
            turn_table="ax_turns_new"
            turn_pk="$(sql_scalar "
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${db}'
  AND TABLE_NAME='ax_turns_new'
  AND COLUMN_KEY='PRI'
ORDER BY ORDINAL_POSITION
LIMIT 1;")"

            "${MYSQL[@]}" -e "SHOW CREATE TABLE \`${db}\`.ax_turns_new;" \
                > "${WORKDIR}/12_${SAFE_DB}_AX_TURNS_NEW_CREATE.tsv" 2>>"$LOGFILE" || true

            if [[ -n "$turn_pk" && -n "$turn_ids" ]]; then
                IDS_SQL="$(printf '%s' "$turn_ids" | tr ',' ' ')"
                IDS_SQL="$(printf '%s\n' "$IDS_SQL" | awk '{for(i=1;i<=NF;i++){if(i>1)printf ","; printf "%d",$i}}')"
                referred_rows="$(sql_scalar "SELECT COUNT(*) FROM \`${db}\`.ax_turns_new WHERE \`${turn_pk}\` IN (${IDS_SQL});")"
                "${MYSQL[@]}" -e "SELECT * FROM \`${db}\`.ax_turns_new WHERE \`${turn_pk}\` IN (${IDS_SQL}) ORDER BY \`${turn_pk}\`;" \
                    > "${WORKDIR}/13_${SAFE_DB}_REFERENCED_TURNS.tsv" 2>>"$LOGFILE" || true
            elif [[ -z "$turn_pk" ]]; then
                warn "${db}.ax_turns_new non ha una chiave primaria rilevabile"
            fi
        else
            warn "${db}: ciclo ${CYCLE_ID} presente ma tabella ax_turns_new assente"
        fi
    fi

    clean_name="$(printf '%s' "$cycle_name" | tr '\t\r\n' '   ')"
    clean_repeat="$(printf '%s' "$cycle_repeat" | tr '\t\r\n' '   ')"
    printf '%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n' \
        "$db" "$cycle_count" "$id_count" "$clean_name" "$clean_repeat" "$turn_ids" "$turn_table" "$turn_pk" "$referred_rows" \
        >> "$SUMMARY_TSV"
done < "$DB_LIST"

report "Database candidati con ciclo ID ${CYCLE_ID}: ${CANDIDATES}"
if [[ "$CANDIDATES" -eq 0 ]]; then
    warn "nessun database locale contiene il ciclo ID ${CYCLE_ID}"
else
    report "Dettagli candidati: 02_CYCLE_ID_${CYCLE_ID}_SUMMARY.tsv"
fi

if [[ "$(table_exists "$PORTAL_DB" ax_turns_cycle)" == "1" ]]; then
    portal_cycle_count="$(sql_scalar "SELECT COUNT(*) FROM \`${PORTAL_DB}\`.ax_turns_cycle WHERE trn_cycle_id=${CYCLE_ID};")"
    report "Nel database destinazione ${PORTAL_DB}, ciclo ID ${CYCLE_ID}: ${portal_cycle_count}"
    if [[ "$portal_cycle_count" != "0" ]]; then
        warn "il ciclo ID ${CYCLE_ID} esiste gia' nella destinazione"
    fi
else
    fail "tabella ${PORTAL_DB}.ax_turns_cycle assente"
fi

{
    echo "AUDIT COMPLETATO"
    echo "Errori: $FAILURES"
    echo "Avvisi: $WARNINGS"
    echo "Database analizzati: $DB_COUNT"
    echo "Candidati ciclo ${CYCLE_ID}: $CANDIDATES"
    echo "Nessuna modifica eseguita."
} > "${WORKDIR}/99_SUMMARY.txt"

log "Audit completato: database=${DB_COUNT}, candidati=${CANDIDATES}, errori=${FAILURES}, avvisi=${WARNINGS}"
exit 0