#!/usr/bin/env bash
# STEP043D - Porting Presenze 215.6
# Fix calendario Silvelox senza modificare il controller PHP.
#
# Ripristina il comportamento legacy impostando i default mancanti
# sulle 6 colonne NOT NULL omesse dai due INSERT originali:
#   cal_act=0
#   cal_url=''
#   cal_category=''
#   cal_repeat_type=''
#   cal_repeat_id=0
#   cal_all_turn=0
#
# Flusso:
# 1. verifica server, controller e schema;
# 2. prova l'INSERT originale su una tabella clonata;
# 3. applica i default alla sola presenze_silvelox.ax_calendar;
# 4. riprova l'INSERT originale su una seconda tabella clonata;
# 5. verifica che il numero di righe reali non sia cambiato;
# 6. rollback automatico dei default in caso di errore successivo.
#
# Server atteso: 215.6 / ax-accessi

set -Eeuo pipefail
umask 077

STEP_NAME="STEP043D_2156_SILVELOX_CALENDAR_LEGACY_DEFAULTS_FIX_PATCH_VERIFIED_20260731"
EXPECTED_HOST="${EXPECTED_HOST:-ax-accessi}"
EXPECTED_IP_FRAGMENT="${EXPECTED_IP_FRAGMENT:-192.168.215.6}"
TARGET_ROOT="${TARGET_ROOT:-/var/www/html/presenze.silvelox}"
TARGET_FILE="${TARGET_FILE:-$TARGET_ROOT/components/templates/custom_templates/ax_attendance/ax_calendar/ctrl/controllerCtrl.php}"
TARGET_DB="${TARGET_DB:-presenze_silvelox}"
ALLOW_TEST="${ALLOW_TEST:-0}"
MYSQL_BIN_OVERRIDE="${MYSQL_BIN_OVERRIDE:-}"

START_TS="$(date +%Y%m%d_%H%M%S)"
OUTPUT_ZIP="$PWD/${STEP_NAME}_${START_TS}.zip"
WORKDIR="$(mktemp -d "$PWD/.${STEP_NAME}.XXXXXX")"
REPORT="$WORKDIR/${STEP_NAME}_REPORT.txt"
SCHEMA_BEFORE="$WORKDIR/schema_before.tsv"
SCHEMA_AFTER="$WORKDIR/schema_after.tsv"
PREFLIGHT_SQL="$WORKDIR/preflight_clone.sql"
PREFLIGHT_OUT="$WORKDIR/preflight_clone.out"
ALTER_REAL_SQL="$WORKDIR/alter_real_defaults.sql"
ALTER_REAL_OUT="$WORKDIR/alter_real_defaults.out"
POSTFLIGHT_SQL="$WORKDIR/postflight_clone.sql"
POSTFLIGHT_OUT="$WORKDIR/postflight_clone.out"
ROLLBACK_SQL="$WORKDIR/rollback_defaults.sql"
ROLLBACK_OUT="$WORKDIR/rollback_defaults.out"
CONTROLLER_COPY="$WORKDIR/controllerCtrl.php.unchanged"

STAMP="$(date +%H%M%S)_$$"
PRE_TABLE="_step043d_pre_${STAMP}"
POST_TABLE="_step043d_post_${STAMP}"

REAL_CHANGED=0
ORIGINAL_STATE=""
MYSQL_BIN=""

cleanup_local() {
    rm -rf -- "$WORKDIR"
}

section() {
    printf '\n\n================================================================\n%s\n================================================================\n' "$1" >> "$REPORT"
}

zip_report() {
    (
        cd "$WORKDIR"
        zip -q -9 -r "$OUTPUT_ZIP" . -x '*.zip'
    )
    zip -T "$OUTPUT_ZIP" >/dev/null
}

drop_test_tables() {
    [[ -n "${MYSQL_BIN:-}" && -x "${MYSQL_BIN:-}" ]] || return 0

    "$MYSQL_BIN" --batch --raw -e \
        "DROP TABLE IF EXISTS \`${TARGET_DB//\`/\`\`}\`.\`${PRE_TABLE}\`;
         DROP TABLE IF EXISTS \`${TARGET_DB//\`/\`\`}\`.\`${POST_TABLE}\`;" \
        >/dev/null 2>&1 || true
}

rollback_defaults() {
    [[ "$REAL_CHANGED" == "1" ]] || return 0

    echo "ROLLBACK: rimozione dei sei default appena applicati." >&2

    cat > "$ROLLBACK_SQL" <<SQL
ALTER TABLE \`${TARGET_DB//\`/\`\`}\`.\`ax_calendar\`
    ALTER COLUMN \`cal_act\` DROP DEFAULT,
    ALTER COLUMN \`cal_url\` DROP DEFAULT,
    ALTER COLUMN \`cal_category\` DROP DEFAULT,
    ALTER COLUMN \`cal_repeat_type\` DROP DEFAULT,
    ALTER COLUMN \`cal_repeat_id\` DROP DEFAULT,
    ALTER COLUMN \`cal_all_turn\` DROP DEFAULT;
SQL

    if "$MYSQL_BIN" --batch --raw \
        < "$ROLLBACK_SQL" > "$ROLLBACK_OUT" 2>&1; then
        echo "ROLLBACK DEFAULT: OK" >> "$REPORT"
        REAL_CHANGED=0
    else
        echo "ROLLBACK DEFAULT: FALLITO" >> "$REPORT"
        cat "$ROLLBACK_OUT" >> "$REPORT"
        cat "$ROLLBACK_OUT" >&2
    fi
}

fatal() {
    local code="$1"
    shift
    trap - ERR
    set +e

    printf 'ERRORE: %s\n' "$*" >&2
    {
        echo
        echo "ERRORE: $*"
        echo "CODICE: $code"
    } >> "$REPORT"

    rollback_defaults
    drop_test_tables

    if zip_report; then
        echo "ZIP DIAGNOSTICO: $OUTPUT_ZIP" >&2
    fi

    exit "$code"
}

on_error() {
    local code=$?
    local line="${1:-?}"
    fatal "$code" "errore inatteso alla linea $line"
}

on_interrupt() {
    fatal 130 "esecuzione interrotta"
}

trap cleanup_local EXIT
trap 'on_error "$LINENO"' ERR
trap on_interrupt INT TERM

HOST_NOW="$(hostname 2>/dev/null || true)"
IPS_NOW="$(hostname -I 2>/dev/null || true)"

{
    echo "$STEP_NAME"
    echo "DATA: $(date --iso-8601=seconds 2>/dev/null || date)"
    echo "SERVER: $HOST_NOW"
    echo "IP: $IPS_NOW"
    echo "PORTALE: $TARGET_ROOT"
    echo "CONTROLLER: $TARGET_FILE"
    echo "DATABASE: $TARGET_DB"
    echo "MODIFICA PHP: NO"
    echo "MODIFICA DB: solo DEFAULT di 6 colonne di ax_calendar"
} > "$REPORT"

if [[ "$ALLOW_TEST" != "1" ]]; then
    if [[ "$HOST_NOW" != "$EXPECTED_HOST" && "$IPS_NOW" != *"$EXPECTED_IP_FRAGMENT"* ]]; then
        fatal 10 "server errato; atteso 215.6 / ax-accessi"
    fi
fi

[[ -f "$TARGET_FILE" ]] || fatal 11 "controller non trovato: $TARGET_FILE"

PHP_BIN="$(command -v php5.6 || command -v php || true)"
[[ -n "$PHP_BIN" ]] || fatal 12 "PHP CLI non disponibile"

if [[ -n "$MYSQL_BIN_OVERRIDE" ]]; then
    MYSQL_BIN="$MYSQL_BIN_OVERRIDE"
else
    MYSQL_BIN="$(command -v mariadb || command -v mysql || true)"
fi
[[ -n "$MYSQL_BIN" && -x "$MYSQL_BIN" ]] || fatal 13 "client MariaDB/MySQL non disponibile"

case "$TARGET_DB" in
    (*[!A-Za-z0-9_]*|'') fatal 14 "nome database non valido: $TARGET_DB" ;;
esac

case "$PRE_TABLE$POST_TABLE" in
    (*[!A-Za-z0-9_]*|'') fatal 15 "nome tabella di test non valido" ;;
esac

section "1. Controller originale invariato"
cp -a -- "$TARGET_FILE" "$CONTROLLER_COPY"

"$PHP_BIN" -l "$TARGET_FILE" >> "$REPORT" 2>&1

OLD_COLUMNS='`cal_destination`,`cal_caus`,`cal_caus_work`,`cal_title`,`cal_description`,`cal_stato`,`cal_start`,`cal_end`,`cal_allDay`,`cal_color`,`cal_note`,`cal_official_festivity`,`cal_time_obj_work`,`cal_time_obj`,`cal_time_index_work`,`cal_time_index`,`cal_special_op`) '
PATCHED_COLUMNS='`cal_special_op`,`cal_act`,`cal_url`,`cal_category`,`cal_repeat_type`,`cal_repeat_id`,`cal_all_turn`'

OLD_COUNT="$(grep -F -o "$OLD_COLUMNS" "$TARGET_FILE" 2>/dev/null | wc -l || true)"
PATCHED_COUNT="$(grep -F -o "$PATCHED_COLUMNS" "$TARGET_FILE" 2>/dev/null | wc -l || true)"

{
    echo "INSERT originali trovati: $OLD_COUNT"
    echo "INSERT PHP gia' modificati trovati: $PATCHED_COUNT"
    sha256sum "$TARGET_FILE"
} >> "$REPORT"

[[ "$OLD_COUNT" -eq 2 ]] || fatal 16 "struttura controller inattesa: INSERT originali=$OLD_COUNT"
[[ "$PATCHED_COUNT" -eq 0 ]] || fatal 17 "controller gia' modificato; nessuna azione eseguita"

section "2. Lettura schema reale prima della modifica"

SCHEMA_QUERY="
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    IF(COLUMN_DEFAULT IS NULL,1,0),
    HEX(IFNULL(COLUMN_DEFAULT,''))
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='ax_calendar'
  AND COLUMN_NAME IN (
      'cal_act','cal_url','cal_category',
      'cal_repeat_type','cal_repeat_id','cal_all_turn'
  )
ORDER BY ORDINAL_POSITION;
"

if "$MYSQL_BIN" --batch --raw --skip-column-names \
    -e "$SCHEMA_QUERY" > "$SCHEMA_BEFORE" 2>&1; then
    :
else
    rc=$?
    cat "$SCHEMA_BEFORE" >&2
    fatal "$rc" "lettura schema ax_calendar fallita"
fi

cat "$SCHEMA_BEFORE" >> "$REPORT"

NULL_COUNT=0
DESIRED_COUNT=0
UNEXPECTED_COUNT=0

while IFS=$'\t' read -r name coltype nullable is_null default_hex; do
    [[ -n "$name" ]] || continue

    if [[ "$is_null" == "1" ]]; then
        NULL_COUNT=$((NULL_COUNT + 1))
        continue
    fi

    case "$name" in
        cal_act|cal_repeat_id|cal_all_turn)
            if [[ "$default_hex" == "30" || "$default_hex" == "273027" ]]; then
                DESIRED_COUNT=$((DESIRED_COUNT + 1))
            else
                UNEXPECTED_COUNT=$((UNEXPECTED_COUNT + 1))
            fi
            ;;
        cal_url|cal_category|cal_repeat_type)
            if [[ -z "$default_hex" || "$default_hex" == "2727" ]]; then
                DESIRED_COUNT=$((DESIRED_COUNT + 1))
            else
                UNEXPECTED_COUNT=$((UNEXPECTED_COUNT + 1))
            fi
            ;;
        *)
            UNEXPECTED_COUNT=$((UNEXPECTED_COUNT + 1))
            ;;
    esac
done < "$SCHEMA_BEFORE"

TOTAL_SCHEMA_ROWS=$((NULL_COUNT + DESIRED_COUNT + UNEXPECTED_COUNT))

{
    echo "Colonne schema trovate: $TOTAL_SCHEMA_ROWS"
    echo "Senza default: $NULL_COUNT"
    echo "Con default legacy corretto: $DESIRED_COUNT"
    echo "Stato inatteso: $UNEXPECTED_COUNT"
} >> "$REPORT"

[[ "$TOTAL_SCHEMA_ROWS" -eq 6 ]] || fatal 18 "schema incompleto: trovate $TOTAL_SCHEMA_ROWS colonne su 6"
[[ "$UNEXPECTED_COUNT" -eq 0 ]] || fatal 19 "schema con default inattesi; nessuna modifica eseguita"

if [[ "$NULL_COUNT" -eq 6 && "$DESIRED_COUNT" -eq 0 ]]; then
    ORIGINAL_STATE="NO_DEFAULTS"
elif [[ "$NULL_COUNT" -eq 0 && "$DESIRED_COUNT" -eq 6 ]]; then
    ORIGINAL_STATE="ALREADY_FIXED"
else
    fatal 20 "schema misto: senza_default=$NULL_COUNT corretti=$DESIRED_COUNT"
fi

echo "Stato iniziale: $ORIGINAL_STATE" >> "$REPORT"

ROW_COUNT_BEFORE_FILE="$WORKDIR/row_count_before.txt"
if "$MYSQL_BIN" --batch --raw --skip-column-names -e \
    "SELECT COUNT(*) FROM \`${TARGET_DB}\`.\`ax_calendar\`;" \
    > "$ROW_COUNT_BEFORE_FILE" 2>&1; then
    :
else
    rc=$?
    cat "$ROW_COUNT_BEFORE_FILE" >&2
    fatal "$rc" "conteggio iniziale ax_calendar fallito"
fi

ROW_COUNT_BEFORE="$(tr -d '\r\n[:space:]' < "$ROW_COUNT_BEFORE_FILE")"
[[ "$ROW_COUNT_BEFORE" =~ ^[0-9]+$ ]] || fatal 21 "conteggio iniziale non valido: $ROW_COUNT_BEFORE"
echo "Righe reali prima: $ROW_COUNT_BEFORE" >> "$REPORT"

section "3. Preflight su clone: default + INSERT originale"

cat > "$PREFLIGHT_SQL" <<SQL
CREATE TABLE \`${TARGET_DB}\`.\`${PRE_TABLE}\`
LIKE \`${TARGET_DB}\`.\`ax_calendar\`;

ALTER TABLE \`${TARGET_DB}\`.\`${PRE_TABLE}\`
    ALTER COLUMN \`cal_act\` SET DEFAULT 0,
    ALTER COLUMN \`cal_url\` SET DEFAULT '',
    ALTER COLUMN \`cal_category\` SET DEFAULT '',
    ALTER COLUMN \`cal_repeat_type\` SET DEFAULT '',
    ALTER COLUMN \`cal_repeat_id\` SET DEFAULT 0,
    ALTER COLUMN \`cal_all_turn\` SET DEFAULT 0;

INSERT INTO \`${TARGET_DB}\`.\`${PRE_TABLE}\`
(
    \`cal_destination\`,
    \`cal_caus\`,
    \`cal_caus_work\`,
    \`cal_title\`,
    \`cal_description\`,
    \`cal_stato\`,
    \`cal_start\`,
    \`cal_end\`,
    \`cal_allDay\`,
    \`cal_color\`,
    \`cal_note\`,
    \`cal_official_festivity\`,
    \`cal_time_obj_work\`,
    \`cal_time_obj\`,
    \`cal_time_index_work\`,
    \`cal_time_index\`,
    \`cal_special_op\`
)
VALUES
(
    56,
    11,
    11,
    'STEP043D TEST',
    'STEP043D TEST',
    'APPROVATO',
    '2026-06-15 00:00:00',
    '2026-06-19 23:59:59',
    '1',
    '',
    'STEP043D TEST',
    0,
    'abse',
    'abse',
    3,
    3,
    '{"noWork":{"move":"abse","to":"abse","caus":"11","time":null},"work":{"move":"abse","to":"abse","caus":"11","time":null}}'
);

SELECT CONCAT_WS(
    '|',
    'TEST',
    COUNT(*),
    MIN(\`cal_act\`),
    HEX(MIN(\`cal_url\`)),
    HEX(MIN(\`cal_category\`)),
    HEX(MIN(\`cal_repeat_type\`)),
    MIN(\`cal_repeat_id\`),
    MIN(\`cal_all_turn\`)
)
FROM \`${TARGET_DB}\`.\`${PRE_TABLE}\`;

DROP TABLE \`${TARGET_DB}\`.\`${PRE_TABLE}\`;
SQL

if "$MYSQL_BIN" --batch --raw --skip-column-names \
    < "$PREFLIGHT_SQL" > "$PREFLIGHT_OUT" 2>&1; then
    :
else
    rc=$?
    cat "$PREFLIGHT_OUT" >&2
    fatal "$rc" "preflight SQL sul clone fallito"
fi

cat "$PREFLIGHT_OUT" >> "$REPORT"
grep -Fxq 'TEST|1|0||||0|0' "$PREFLIGHT_OUT" || \
    fatal 22 "preflight SQL non ha restituito i default legacy attesi"

section "4. Applicazione default alla tabella reale"

cat > "$ALTER_REAL_SQL" <<SQL
ALTER TABLE \`${TARGET_DB}\`.\`ax_calendar\`
    ALTER COLUMN \`cal_act\` SET DEFAULT 0,
    ALTER COLUMN \`cal_url\` SET DEFAULT '',
    ALTER COLUMN \`cal_category\` SET DEFAULT '',
    ALTER COLUMN \`cal_repeat_type\` SET DEFAULT '',
    ALTER COLUMN \`cal_repeat_id\` SET DEFAULT 0,
    ALTER COLUMN \`cal_all_turn\` SET DEFAULT 0;
SQL

if [[ "$ORIGINAL_STATE" == "ALREADY_FIXED" ]]; then
    echo "Default gia' corretti: ALTER reale non necessario." >> "$REPORT"
else
    if "$MYSQL_BIN" --batch --raw \
        < "$ALTER_REAL_SQL" > "$ALTER_REAL_OUT" 2>&1; then
        REAL_CHANGED=1
    else
        rc=$?
        cat "$ALTER_REAL_OUT" >&2
        fatal "$rc" "ALTER TABLE reale fallito"
    fi
    cat "$ALTER_REAL_OUT" >> "$REPORT"
fi

section "5. Verifica schema dopo la modifica"

if "$MYSQL_BIN" --batch --raw --skip-column-names \
    -e "$SCHEMA_QUERY" > "$SCHEMA_AFTER" 2>&1; then
    :
else
    rc=$?
    cat "$SCHEMA_AFTER" >&2
    fatal "$rc" "lettura schema dopo ALTER fallita"
fi

cat "$SCHEMA_AFTER" >> "$REPORT"

POST_DESIRED=0
POST_OTHER=0

while IFS=$'\t' read -r name coltype nullable is_null default_hex; do
    [[ -n "$name" ]] || continue

    if [[ "$is_null" == "1" ]]; then
        POST_OTHER=$((POST_OTHER + 1))
        continue
    fi

    case "$name" in
        cal_act|cal_repeat_id|cal_all_turn)
            if [[ "$default_hex" == "30" || "$default_hex" == "273027" ]]; then
                POST_DESIRED=$((POST_DESIRED + 1))
            else
                POST_OTHER=$((POST_OTHER + 1))
            fi
            ;;
        cal_url|cal_category|cal_repeat_type)
            if [[ -z "$default_hex" || "$default_hex" == "2727" ]]; then
                POST_DESIRED=$((POST_DESIRED + 1))
            else
                POST_OTHER=$((POST_OTHER + 1))
            fi
            ;;
        *)
            POST_OTHER=$((POST_OTHER + 1))
            ;;
    esac
done < "$SCHEMA_AFTER"

[[ "$POST_DESIRED" -eq 6 && "$POST_OTHER" -eq 0 ]] || \
    fatal 23 "verifica default reali fallita: corretti=$POST_DESIRED altri=$POST_OTHER"

section "6. Postflight: INSERT originale su clone della tabella reale"

cat > "$POSTFLIGHT_SQL" <<SQL
CREATE TABLE \`${TARGET_DB}\`.\`${POST_TABLE}\`
LIKE \`${TARGET_DB}\`.\`ax_calendar\`;

INSERT INTO \`${TARGET_DB}\`.\`${POST_TABLE}\`
(
    \`cal_destination\`,
    \`cal_caus\`,
    \`cal_caus_work\`,
    \`cal_title\`,
    \`cal_description\`,
    \`cal_stato\`,
    \`cal_start\`,
    \`cal_end\`,
    \`cal_allDay\`,
    \`cal_color\`,
    \`cal_note\`,
    \`cal_official_festivity\`,
    \`cal_time_obj_work\`,
    \`cal_time_obj\`,
    \`cal_time_index_work\`,
    \`cal_time_index\`,
    \`cal_special_op\`
)
VALUES
(
    56,
    11,
    11,
    'STEP043D TEST',
    'STEP043D TEST',
    'APPROVATO',
    '2026-06-15 00:00:00',
    '2026-06-19 23:59:59',
    '1',
    '',
    'STEP043D TEST',
    0,
    'abse',
    'abse',
    3,
    3,
    '{"noWork":{"move":"abse","to":"abse","caus":"11","time":null},"work":{"move":"abse","to":"abse","caus":"11","time":null}}'
);

SELECT CONCAT_WS(
    '|',
    'TEST',
    COUNT(*),
    MIN(\`cal_act\`),
    HEX(MIN(\`cal_url\`)),
    HEX(MIN(\`cal_category\`)),
    HEX(MIN(\`cal_repeat_type\`)),
    MIN(\`cal_repeat_id\`),
    MIN(\`cal_all_turn\`)
)
FROM \`${TARGET_DB}\`.\`${POST_TABLE}\`;

DROP TABLE \`${TARGET_DB}\`.\`${POST_TABLE}\`;
SQL

if "$MYSQL_BIN" --batch --raw --skip-column-names \
    < "$POSTFLIGHT_SQL" > "$POSTFLIGHT_OUT" 2>&1; then
    :
else
    rc=$?
    cat "$POSTFLIGHT_OUT" >&2
    fatal "$rc" "postflight SQL sul clone fallito"
fi

cat "$POSTFLIGHT_OUT" >> "$REPORT"
grep -Fxq 'TEST|1|0||||0|0' "$POSTFLIGHT_OUT" || \
    fatal 24 "postflight SQL non ha restituito i default attesi"

section "7. Integrita' dati reali"

ROW_COUNT_AFTER_FILE="$WORKDIR/row_count_after.txt"
if "$MYSQL_BIN" --batch --raw --skip-column-names -e \
    "SELECT COUNT(*) FROM \`${TARGET_DB}\`.\`ax_calendar\`;" \
    > "$ROW_COUNT_AFTER_FILE" 2>&1; then
    :
else
    rc=$?
    cat "$ROW_COUNT_AFTER_FILE" >&2
    fatal "$rc" "conteggio finale ax_calendar fallito"
fi

ROW_COUNT_AFTER="$(tr -d '\r\n[:space:]' < "$ROW_COUNT_AFTER_FILE")"
[[ "$ROW_COUNT_AFTER" =~ ^[0-9]+$ ]] || fatal 25 "conteggio finale non valido: $ROW_COUNT_AFTER"
[[ "$ROW_COUNT_AFTER" == "$ROW_COUNT_BEFORE" ]] || \
    fatal 26 "numero righe reale modificato: prima=$ROW_COUNT_BEFORE dopo=$ROW_COUNT_AFTER"

cmp -s "$TARGET_FILE" "$CONTROLLER_COPY" || \
    fatal 27 "il controller PHP e' cambiato, ma non doveva essere modificato"

{
    echo "Righe reali prima: $ROW_COUNT_BEFORE"
    echo "Righe reali dopo: $ROW_COUNT_AFTER"
    echo "Controller PHP invariato: SI"
    echo "Tabelle di test permanenti: NO"
    echo "Reload servizi: NON NECESSARIO"
    echo "ESITO: COMPLETATO"
} >> "$REPORT"

drop_test_tables
zip_report

REAL_CHANGED=0

echo "STEP043D COMPLETATO"
echo "ZIP: $OUTPUT_ZIP"