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

# STEP009 - SERVER ATTESO: 215.6 / ax-accessi
#
# Modifica esclusivamente il database demo presenze_distribuite:
# 1. normalizza il turno demo 1 da bands=3 a bands=1;
# 2. copia da presenze_silvelox la riga Standard con adj_code=0000;
# 3. crea il ciclo demo 2026 dal ciclo Silvelox 15, sostituendo turn=15 con turn=1.
#
# Silvelox viene usato esclusivamente in lettura.
# In caso di verifica finale fallita esegue automaticamente il rollback.
# Lascia nella cartella di lancio soltanto lo ZIP finale.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP009_2156_DISTRIBUITE_DEMO_TURN_DEPENDENCIES_PATCH_TEST_COMMIT_ZIP_ONLY_${STAMP}"
WORKDIR="${START_DIR}/.${BASE}_work_$$"
OUTZIP="${START_DIR}/${BASE}.zip"
LOGFILE="${WORKDIR}/run.log"
REPORT="${WORKDIR}/report.txt"
PATCH_SQL="${WORKDIR}/patch.sql"
ROLLBACK_SQL="${WORKDIR}/rollback.sql"
PACKAGED=0
COMMITTED=0
ROLLBACK_DONE=0
FAILURES=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}"
TARGET_TURN_ID="${STEP_TARGET_TURN_ID:-1}"
TARGET_BAND_ID="${STEP_TARGET_BAND_ID:-1}"
SOURCE_BAND_ID="${STEP_SOURCE_BAND_ID:-3}"
TARGET_CYCLE_ID="${STEP_TARGET_CYCLE_ID:-1}"
SOURCE_CYCLE_ID="${STEP_SOURCE_CYCLE_ID:-15}"
CYCLE_NAME="Ciclo Demo 2026"
CYCLE_NOTE="Dati demo Distribuite - modello Silvelox 2026"

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: $*"
}

package_results() {
    local rc="${1:-0}"
    local zip_rc=0

    [[ "$PACKAGED" -eq 1 ]] && return 0
    PACKAGED=1

    report ""
    report "Esito finale: errori=${FAILURES}, committed=${COMMITTED}, rollback=${ROLLBACK_DONE}, 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 "TARGET: ${TARGET_DB}"
log "SORGENTE READ-ONLY: ${SOURCE_DB}"
log "MODALITA': PATCH + TEST + COMMIT"
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': PATCH + TEST + COMMIT"
    echo "Data: $(date --iso-8601=seconds)"
    echo "Hostname: $ACTUAL_HOST"
    echo "IP: $ACTUAL_IPS"
    echo "Database target: $TARGET_DB"
    echo "Database sorgente read-only: $SOURCE_DB"
    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

for value in \
    "$TARGET_TURN_ID" \
    "$TARGET_BAND_ID" \
    "$SOURCE_BAND_ID" \
    "$TARGET_CYCLE_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)
MYSQL_SCALAR=("$MYSQL_BIN" --batch --raw --skip-column-names --default-character-set=utf8mb4)

if ! "${MYSQL_SCALAR[@]}" -e 'SELECT 1' >/dev/null 2>&1; then
    fail "accesso MySQL locale non riuscito"
    exit 23
fi

sql_scalar() {
    "${MYSQL_SCALAR[@]}" -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';"
}

count_token() {
    local db="$1"
    local table="$2"
    local column="$3"
    local where_sql="$4"
    local token="$5"

    sql_scalar "
SELECT
    (CHAR_LENGTH(\`${column}\`) -
     CHAR_LENGTH(REPLACE(\`${column}\`, '${token}', '')))
    / CHAR_LENGTH('${token}')
FROM \`${db}\`.\`${table}\`
WHERE ${where_sql};"
}

log "[1/7] Controlli schema e precondizioni"

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

for specification in \
    "ax_turns_new:trn_id" \
    "ax_turns_new:trn_work_days" \
    "ax_turns_cycle:trn_cycle_id" \
    "ax_turns_cycle:trn_cycle_repeat" \
    "ax_turns_cycle:trn_cycle_model" \
    "ax_turn_adjust:adj_code" \
    "ax_bands:bnd_id"
do
    table="${specification%%:*}"
    column="${specification##*:}"

    [[ "$(column_exists "$TARGET_DB" "$table" "$column")" == "1" ]] \
        || fail "colonna target assente: ${TARGET_DB}.${table}.${column}"

    [[ "$(column_exists "$SOURCE_DB" "$table" "$column")" == "1" ]] \
        || fail "colonna sorgente assente: ${SOURCE_DB}.${table}.${column}"
done

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

TARGET_TURN_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TARGET_TURN_ID};")"

TARGET_BAND_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_bands
WHERE bnd_id=${TARGET_BAND_ID};")"

TARGET_OLD_BAND_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_bands
WHERE bnd_id=${SOURCE_BAND_ID};")"

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

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

TARGET_CYCLE_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${TARGET_CYCLE_ID};")"

TARGET_ALL_CYCLES="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_turns_cycle;")"

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

TARGET_TURN_JSON_VALID="$(sql_scalar "
SELECT JSON_VALID(trn_work_days)
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TARGET_TURN_ID};")"

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

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

TARGET_BAND_3_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"bands":"3"')"

TARGET_BAND_1_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"bands":"1"')"

TARGET_ADJUST_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"adjust":"0000"')"

SOURCE_REPEAT_TURN_15="$(count_token \
    "$SOURCE_DB" ax_turns_cycle trn_cycle_repeat \
    "trn_cycle_id=${SOURCE_CYCLE_ID}" \
    '"turn":"15"')"

SOURCE_MODEL_TURN_15="$(count_token \
    "$SOURCE_DB" ax_turns_cycle trn_cycle_model \
    "trn_cycle_id=${SOURCE_CYCLE_ID}" \
    '"turn":"15"')"

SOURCE_MODEL_2026_DATES="$(count_token \
    "$SOURCE_DB" ax_turns_cycle trn_cycle_model \
    "trn_cycle_id=${SOURCE_CYCLE_ID}" \
    '"from":"2026-')"

{
    printf 'controllo\tvalore_atteso\tvalore_rilevato\n'
    printf 'target_turn_count\t1\t%s\n' "$TARGET_TURN_COUNT"
    printf 'target_band_1_count\t1\t%s\n' "$TARGET_BAND_COUNT"
    printf 'target_band_3_count\t0\t%s\n' "$TARGET_OLD_BAND_COUNT"
    printf 'target_adjust_code_0000\t0\t%s\n' "$TARGET_ADJUST_COUNT"
    printf 'source_adjust_code_0000\t1\t%s\n' "$SOURCE_ADJUST_COUNT"
    printf 'target_cycle_id_1\t0\t%s\n' "$TARGET_CYCLE_COUNT"
    printf 'target_all_cycles\t0\t%s\n' "$TARGET_ALL_CYCLES"
    printf 'source_cycle_id_15\t1\t%s\n' "$SOURCE_CYCLE_COUNT"
    printf 'target_turn_json_valid\t1\t%s\n' "$TARGET_TURN_JSON_VALID"
    printf 'source_repeat_json_valid\t1\t%s\n' "$SOURCE_REPEAT_JSON_VALID"
    printf 'source_model_json_valid\t1\t%s\n' "$SOURCE_MODEL_JSON_VALID"
    printf 'target_refs_bands_3\t7\t%s\n' "$TARGET_BAND_3_REFS"
    printf 'target_refs_bands_1\t0\t%s\n' "$TARGET_BAND_1_REFS"
    printf 'target_refs_adjust_0000\t7\t%s\n' "$TARGET_ADJUST_REFS"
    printf 'source_repeat_refs_turn_15\t4\t%s\n' "$SOURCE_REPEAT_TURN_15"
    printf 'source_model_refs_turn_15\t365\t%s\n' "$SOURCE_MODEL_TURN_15"
    printf 'source_model_dates_2026\t365\t%s\n' "$SOURCE_MODEL_2026_DATES"
} >"${WORKDIR}/01_PRECHECKS.tsv"

[[ "$TARGET_TURN_COUNT" == "1" ]] || fail "turno demo target non univoco"
[[ "$TARGET_BAND_COUNT" == "1" ]] || fail "fascia demo target 1 non univoca"
[[ "$TARGET_OLD_BAND_COUNT" == "0" ]] || fail "fascia target 3 inattesa"
[[ "$TARGET_ADJUST_COUNT" == "0" ]] || fail "adj_code=0000 gia' presente nel target"
[[ "$SOURCE_ADJUST_COUNT" == "1" ]] || fail "adj_code=0000 sorgente non univoco"
[[ "$TARGET_CYCLE_COUNT" == "0" ]] || fail "ciclo target 1 gia' presente"
[[ "$TARGET_ALL_CYCLES" == "0" ]] || fail "target contiene gia' cicli inattesi"
[[ "$SOURCE_CYCLE_COUNT" == "1" ]] || fail "ciclo sorgente 15 non univoco"
[[ "$TARGET_TURN_JSON_VALID" == "1" ]] || fail "JSON turno target non valido"
[[ "$SOURCE_REPEAT_JSON_VALID" == "1" ]] || fail "JSON repeat sorgente non valido"
[[ "$SOURCE_MODEL_JSON_VALID" == "1" ]] || fail "JSON model sorgente non valido"
[[ "$TARGET_BAND_3_REFS" == "7" ]] || fail "riferimenti bands=3 inattesi: $TARGET_BAND_3_REFS"
[[ "$TARGET_BAND_1_REFS" == "0" ]] || fail "riferimenti bands=1 gia' presenti: $TARGET_BAND_1_REFS"
[[ "$TARGET_ADJUST_REFS" == "7" ]] || fail "riferimenti adjust=0000 inattesi: $TARGET_ADJUST_REFS"
[[ "$SOURCE_REPEAT_TURN_15" == "4" ]] || fail "repeat sorgente con turn=15 inatteso: $SOURCE_REPEAT_TURN_15"
[[ "$SOURCE_MODEL_TURN_15" == "365" ]] || fail "model sorgente con turn=15 inatteso: $SOURCE_MODEL_TURN_15"
[[ "$SOURCE_MODEL_2026_DATES" == "365" ]] || fail "copertura date 2026 inattesa: $SOURCE_MODEL_2026_DATES"

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

log "[2/7] Esportazione righe prima della modifica"

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

SELECT *
FROM \`${TARGET_DB}\`.ax_turn_adjust
ORDER BY adj_id;

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

"${MYSQL[@]}" -e "
SELECT *
FROM \`${SOURCE_DB}\`.ax_turn_adjust
WHERE adj_code='0000';

SELECT *
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};" \
    >"${WORKDIR}/03_SOURCE_ROWS.tsv" 2>>"$LOGFILE"

log "[3/7] Generazione rollback verificato"

{
    echo "START TRANSACTION;"
    echo "DELETE FROM \`${TARGET_DB}\`.ax_turns_cycle"
    echo "WHERE trn_cycle_id=${TARGET_CYCLE_ID}"
    echo "  AND trn_cycle_name='${CYCLE_NAME}';"
    echo
    echo "DELETE FROM \`${TARGET_DB}\`.ax_turn_adjust"
    echo "WHERE adj_code='0000';"
    echo
} >"$ROLLBACK_SQL"

"${MYSQL_SCALAR[@]}" -e "
SELECT CONCAT(
    'UPDATE \`${TARGET_DB}\`.ax_turns_new SET trn_work_days=',
    QUOTE(trn_work_days),
    ' WHERE trn_id=${TARGET_TURN_ID};'
)
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TARGET_TURN_ID};" >>"$ROLLBACK_SQL" 2>>"$LOGFILE"

echo "COMMIT;" >>"$ROLLBACK_SQL"

grep -q '^UPDATE `presenze_distribuite`\.ax_turns_new SET trn_work_days=' "$ROLLBACK_SQL" \
    || {
        fail "rollback SQL incompleto"
        exit 26
    }

log "[4/7] Generazione ed esecuzione patch SQL"

cat >"$PATCH_SQL" <<SQL
START TRANSACTION;

INSERT INTO \`${TARGET_DB}\`.ax_turn_adjust
(
    adj_cat,
    adj_code,
    adj_name,
    adj_on_csntr,
    adj_on_trn,
    adj_condition,
    adj_add,
    adj_hid,
    adj_sub,
    adj_msg,
    adj_on_day
)
SELECT
    adj_cat,
    adj_code,
    adj_name,
    adj_on_csntr,
    adj_on_trn,
    adj_condition,
    adj_add,
    adj_hid,
    adj_sub,
    adj_msg,
    adj_on_day
FROM \`${SOURCE_DB}\`.ax_turn_adjust
WHERE adj_code='0000';

UPDATE \`${TARGET_DB}\`.ax_turns_new
SET trn_work_days = REPLACE(
    trn_work_days,
    '"bands":"${SOURCE_BAND_ID}"',
    '"bands":"${TARGET_BAND_ID}"'
)
WHERE trn_id=${TARGET_TURN_ID};

INSERT INTO \`${TARGET_DB}\`.ax_turns_cycle
(
    trn_cycle_id,
    trn_cycle_name,
    trn_cycle_repeat,
    trn_cycle_model,
    trn_cycle_enable,
    trn_cycle_note,
    trn_cycle_typ
)
SELECT
    ${TARGET_CYCLE_ID},
    '${CYCLE_NAME}',
    REPLACE(
        trn_cycle_repeat,
        '"turn":"${SOURCE_CYCLE_ID}"',
        '"turn":"${TARGET_TURN_ID}"'
    ),
    REPLACE(
        trn_cycle_model,
        '"turn":"${SOURCE_CYCLE_ID}"',
        '"turn":"${TARGET_TURN_ID}"'
    ),
    trn_cycle_enable,
    '${CYCLE_NOTE}',
    trn_cycle_typ
FROM \`${SOURCE_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${SOURCE_CYCLE_ID};

COMMIT;
SQL

if ! "${MYSQL[@]}" --show-warnings <"$PATCH_SQL" >>"$LOGFILE" 2>&1; then
    fail "esecuzione patch SQL fallita; la connessione e' stata chiusa senza COMMIT valido"
    exit 27
fi

COMMITTED=1
report "Patch SQL eseguita e committata."

log "[5/7] Verifica completa dopo il commit"

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

POST_TURN_JSON_VALID="$(sql_scalar "
SELECT JSON_VALID(trn_work_days)
FROM \`${TARGET_DB}\`.ax_turns_new
WHERE trn_id=${TARGET_TURN_ID};")"

POST_BAND_3_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"bands":"3"')"

POST_BAND_1_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"bands":"1"')"

POST_ADJUST_REFS="$(count_token \
    "$TARGET_DB" ax_turns_new trn_work_days \
    "trn_id=${TARGET_TURN_ID}" \
    '"adjust":"0000"')"

POST_CYCLE_COUNT="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${TARGET_CYCLE_ID}
  AND trn_cycle_name='${CYCLE_NAME}';")"

POST_REPEAT_JSON_VALID="$(sql_scalar "
SELECT JSON_VALID(trn_cycle_repeat)
FROM \`${TARGET_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${TARGET_CYCLE_ID};")"

POST_MODEL_JSON_VALID="$(sql_scalar "
SELECT JSON_VALID(trn_cycle_model)
FROM \`${TARGET_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${TARGET_CYCLE_ID};")"

POST_REPEAT_TURN_1="$(count_token \
    "$TARGET_DB" ax_turns_cycle trn_cycle_repeat \
    "trn_cycle_id=${TARGET_CYCLE_ID}" \
    '"turn":"1"')"

POST_REPEAT_TURN_15="$(count_token \
    "$TARGET_DB" ax_turns_cycle trn_cycle_repeat \
    "trn_cycle_id=${TARGET_CYCLE_ID}" \
    '"turn":"15"')"

POST_MODEL_TURN_1="$(count_token \
    "$TARGET_DB" ax_turns_cycle trn_cycle_model \
    "trn_cycle_id=${TARGET_CYCLE_ID}" \
    '"turn":"1"')"

POST_MODEL_TURN_15="$(count_token \
    "$TARGET_DB" ax_turns_cycle trn_cycle_model \
    "trn_cycle_id=${TARGET_CYCLE_ID}" \
    '"turn":"15"')"

POST_MODEL_2026_DATES="$(count_token \
    "$TARGET_DB" ax_turns_cycle trn_cycle_model \
    "trn_cycle_id=${TARGET_CYCLE_ID}" \
    '"from":"2026-')"

{
    printf 'controllo\tvalore_atteso\tvalore_rilevato\n'
    printf 'adjust_code_0000\t1\t%s\n' "$POST_ADJUST_COUNT"
    printf 'turn_json_valid\t1\t%s\n' "$POST_TURN_JSON_VALID"
    printf 'turn_refs_bands_3\t0\t%s\n' "$POST_BAND_3_REFS"
    printf 'turn_refs_bands_1\t7\t%s\n' "$POST_BAND_1_REFS"
    printf 'turn_refs_adjust_0000\t7\t%s\n' "$POST_ADJUST_REFS"
    printf 'cycle_id_1\t1\t%s\n' "$POST_CYCLE_COUNT"
    printf 'cycle_repeat_json_valid\t1\t%s\n' "$POST_REPEAT_JSON_VALID"
    printf 'cycle_model_json_valid\t1\t%s\n' "$POST_MODEL_JSON_VALID"
    printf 'cycle_repeat_turn_1\t4\t%s\n' "$POST_REPEAT_TURN_1"
    printf 'cycle_repeat_turn_15\t0\t%s\n' "$POST_REPEAT_TURN_15"
    printf 'cycle_model_turn_1\t365\t%s\n' "$POST_MODEL_TURN_1"
    printf 'cycle_model_turn_15\t0\t%s\n' "$POST_MODEL_TURN_15"
    printf 'cycle_model_dates_2026\t365\t%s\n' "$POST_MODEL_2026_DATES"
} >"${WORKDIR}/04_POSTCHECKS.tsv"

VERIFY_OK=1

[[ "$POST_ADJUST_COUNT" == "1" ]] || VERIFY_OK=0
[[ "$POST_TURN_JSON_VALID" == "1" ]] || VERIFY_OK=0
[[ "$POST_BAND_3_REFS" == "0" ]] || VERIFY_OK=0
[[ "$POST_BAND_1_REFS" == "7" ]] || VERIFY_OK=0
[[ "$POST_ADJUST_REFS" == "7" ]] || VERIFY_OK=0
[[ "$POST_CYCLE_COUNT" == "1" ]] || VERIFY_OK=0
[[ "$POST_REPEAT_JSON_VALID" == "1" ]] || VERIFY_OK=0
[[ "$POST_MODEL_JSON_VALID" == "1" ]] || VERIFY_OK=0
[[ "$POST_REPEAT_TURN_1" == "4" ]] || VERIFY_OK=0
[[ "$POST_REPEAT_TURN_15" == "0" ]] || VERIFY_OK=0
[[ "$POST_MODEL_TURN_1" == "365" ]] || VERIFY_OK=0
[[ "$POST_MODEL_TURN_15" == "0" ]] || VERIFY_OK=0
[[ "$POST_MODEL_2026_DATES" == "365" ]] || VERIFY_OK=0

if [[ "$VERIFY_OK" -ne 1 ]]; then
    fail "verifica finale incoerente: eseguo il rollback automatico"

    if "${MYSQL[@]}" --show-warnings <"$ROLLBACK_SQL" >>"$LOGFILE" 2>&1; then
        ROLLBACK_DONE=1
        COMMITTED=0
        report "Rollback automatico completato."
    else
        fail "ROLLBACK AUTOMATICO FALLITO: eseguire manualmente rollback.sql contenuto nello ZIP"
    fi

    exit 28
fi

log "[6/7] Esportazione stato finale"

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

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

SELECT *
FROM \`${TARGET_DB}\`.ax_turns_cycle
WHERE trn_cycle_id=${TARGET_CYCLE_ID};" \
    >"${WORKDIR}/05_TARGET_AFTER.tsv" 2>>"$LOGFILE"

log "[7/7] Esito"

{
    echo "PATCH COMPLETATA"
    echo "Database modificato: $TARGET_DB"
    echo "Silvelox modificato: NO"
    echo "Turno 1: bands=3 sostituito con bands=1 in 7 giorni"
    echo "Arrotondamento Standard adj_code=0000: inserito"
    echo "Ciclo Demo 2026: inserito con 365 giorni sul turno 1"
    echo "JSON turno/ciclo: validi"
    echo "Rollback automatico: non necessario"
} >"${WORKDIR}/99_SUMMARY.txt"

report "Turno demo normalizzato: bands=1 su 7 giorni."
report "Arrotondamento Standard adj_code=0000 inserito."
report "Ciclo Demo 2026 inserito: 365 giorni sul turno 1."
report "Silvelox non modificato."

log "PATCH COMPLETATA E VERIFICATA"