#!/usr/bin/env bash
# STEP043C - Porting Presenze 215.6
# Correzione salvataggio calendario Silvelox.
#
# Differenza rispetto a STEP043/STEP043B:
# - verifica PRIMA il nuovo INSERT su tabella TEMPORARY;
# - in caso di errore mostra a video il messaggio MariaDB completo;
# - modifica il controller solo dopo il superamento del test SQL.
#
# Server atteso: 215.6 / ax-accessi
# Portale: /var/www/html/presenze.silvelox

set -Eeuo pipefail
umask 077

STEP_NAME="STEP043C_2156_SILVELOX_CALENDAR_INSERT_REQUIRED_COLUMNS_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}"
TARGET_HOST="${TARGET_HOST:-presenze.silvelox.axuno.it}"
ALLOW_TEST="${ALLOW_TEST:-0}"
SKIP_SERVICE_RELOAD="${SKIP_SERVICE_RELOAD:-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"
BACKUP_FILE="$WORKDIR/controllerCtrl.php.before"
PATCHED_COPY="$WORKDIR/controllerCtrl.php.after"
SQL_TEST="$WORKDIR/new_insert_preflight.sql"
SQL_OUTPUT="$WORKDIR/new_insert_preflight_output.txt"
PATCH_ACTIVE=0

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

reload_runtime() {
    if [[ "$SKIP_SERVICE_RELOAD" == "1" ]]; then
        return 0
    fi

    if command -v systemctl >/dev/null 2>&1 &&
       systemctl list-unit-files 2>/dev/null | grep -q '^php5\.6-fpm\.service'; then
        systemctl reload php5.6-fpm
    elif command -v service >/dev/null 2>&1; then
        service php5.6-fpm reload
    fi

    if command -v apache2ctl >/dev/null 2>&1; then
        apache2ctl configtest
        apache2ctl graceful
    fi
}

rollback() {
    if [[ "$PATCH_ACTIVE" == "1" && -f "$BACKUP_FILE" ]]; then
        echo "ROLLBACK: ripristino del controller originale." >&2
        cp -a -- "$BACKUP_FILE" "$TARGET_FILE"
        reload_runtime >/dev/null 2>&1 || true
        PATCH_ACTIVE=0
    fi
}

on_error() {
    local rc=$?
    local line="${1:-?}"
    rollback
    printf 'ERRORE linea %s, codice %s\n' "$line" "$rc" >&2
    exit "$rc"
}

on_interrupt() {
    rollback
    cleanup
    exit 130
}

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

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

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

if [[ "$ALLOW_TEST" != "1" ]]; then
    if [[ "$HOST_NOW" != "$EXPECTED_HOST" && "$IPS_NOW" != *"$EXPECTED_IP_FRAGMENT"* ]]; then
        echo "ERRORE: server errato. Rilevato host='$HOST_NOW' IP='$IPS_NOW'; atteso 215.6 / ax-accessi." >&2
        exit 10
    fi
fi

[[ -f "$TARGET_FILE" ]] || {
    echo "ERRORE: controller non trovato: $TARGET_FILE" >&2
    exit 11
}

PHP_BIN="$(command -v php5.6 || command -v php || true)"
[[ -n "$PHP_BIN" ]] || {
    echo "ERRORE: PHP CLI non disponibile." >&2
    exit 12
}

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" ]] || {
    echo "ERRORE: client MariaDB/MySQL non disponibile." >&2
    exit 13
}

{
    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 "FILE: $TARGET_FILE"
    echo "DATABASE: $TARGET_DB"
    echo "MYSQL_BIN: $MYSQL_BIN"
    echo "MODIFICA: solo i due INSERT di ax_calendar"
} > "$REPORT"

section "Pre-check file"
{
    stat -c '%A %a %U:%G %s %y %n' "$TARGET_FILE"
    sha256sum "$TARGET_FILE"
    "$PHP_BIN" -l "$TARGET_FILE"
} >> "$REPORT" 2>&1

section "Pre-check schema obbligatorio"
SCHEMA_OUTPUT="$WORKDIR/schema_required_columns.txt"

if ! "$MYSQL_BIN" --batch --raw --skip-column-names -e \
    "SELECT CONCAT(
        COLUMN_NAME,'|',COLUMN_TYPE,'|',IS_NULLABLE,'|',
        IFNULL(COLUMN_DEFAULT,'<NULL>')
     )
     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;" \
    > "$SCHEMA_OUTPUT" 2>&1; then
    echo "ERRORE: impossibile leggere lo schema di ax_calendar." >&2
    cat "$SCHEMA_OUTPUT" >&2
    exit 20
fi

cat "$SCHEMA_OUTPUT" >> "$REPORT"

for required_column in \
    cal_act cal_url cal_category cal_repeat_type cal_repeat_id cal_all_turn; do
    if ! grep -Eq "^${required_column}\|" "$SCHEMA_OUTPUT"; then
        echo "ERRORE: colonna attesa non trovata: $required_column" >&2
        exit 21
    fi
done

section "Preflight del nuovo INSERT su tabella TEMPORARY"

cat > "$SQL_TEST" <<SQL
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE TEMPORARY TABLE _step043c_ax_calendar_new
LIKE \`${TARGET_DB//\`/\`\`}\`.\`ax_calendar\`;

INSERT INTO _step043c_ax_calendar_new
(
    \`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\`,
    \`cal_act\`,
    \`cal_url\`,
    \`cal_category\`,
    \`cal_repeat_type\`,
    \`cal_repeat_id\`,
    \`cal_all_turn\`
)
VALUES
(
    1,
    1,
    1,
    'STEP043C TEST',
    'STEP043C TEST',
    'APPROVATO',
    '2026-01-01 00:00:00',
    '2026-01-01 23:59:59',
    '1',
    '',
    'STEP043C TEST',
    0,
    'abse',
    'abse',
    3,
    3,
    '{}',
    0,
    '',
    '',
    '',
    0,
    0
);

SELECT CONCAT('TEMP_ROWS=', COUNT(*))
FROM _step043c_ax_calendar_new;
SQL

set +e
"$MYSQL_BIN" --batch --raw --skip-column-names \
    < "$SQL_TEST" > "$SQL_OUTPUT" 2>&1
SQL_RC=$?
set -e

cat "$SQL_OUTPUT" >> "$REPORT"

if [[ "$SQL_RC" -ne 0 ]]; then
    echo
    echo "ERRORE NEL TEST SQL. IL CONTROLLER NON E' STATO MODIFICATO." >&2
    echo "Messaggio MariaDB/MySQL:" >&2
    cat "$SQL_OUTPUT" >&2
    exit 22
fi

if ! grep -Fxq 'TEMP_ROWS=1' "$SQL_OUTPUT"; then
    echo "ERRORE: il nuovo INSERT non ha prodotto TEMP_ROWS=1." >&2
    cat "$SQL_OUTPUT" >&2
    exit 23
fi

echo "TEST SQL TEMPORANEO: OK"
echo "Nessun record scritto nella tabella reale ax_calendar."

cp -a -- "$TARGET_FILE" "$BACKUP_FILE"

PATCHER="$WORKDIR/patch_controller.php"
cat > "$PATCHER" <<'PHP'
<?php
if ($argc !== 2) {
    fwrite(STDERR, "Uso: patch_controller.php FILE\n");
    exit(2);
}

$file = $argv[1];
$data = file_get_contents($file);

if ($data === false) {
    fwrite(STDERR, "Impossibile leggere il file\n");
    exit(3);
}

$oldColumns =
    '`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`) ';

$newColumns =
    '`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`,`cal_act`,`cal_url`,' .
    '`cal_category`,`cal_repeat_type`,`cal_repeat_id`,`cal_all_turn`) ';

$oldValues = '\'$specialop\')";';
$newValues = '\'$specialop\',\'0\',\'\',\'\',\'\',\'0\',\'0\')";';

$oldColumnsCount = substr_count($data, $oldColumns);
$newColumnsCount = substr_count($data, $newColumns);
$oldValuesCount = substr_count($data, $oldValues);
$newValuesCount = substr_count($data, $newValues);

if (
    $newColumnsCount === 2 &&
    $newValuesCount === 2 &&
    $oldColumnsCount === 0 &&
    $oldValuesCount === 0
) {
    echo "PATCH_GIA_PRESENTE\n";
    exit(0);
}

if (
    $oldColumnsCount !== 2 ||
    $oldValuesCount !== 2 ||
    $newColumnsCount !== 0 ||
    $newValuesCount !== 0
) {
    fwrite(
        STDERR,
        "Struttura inattesa: " .
        "oldColumns=$oldColumnsCount " .
        "oldValues=$oldValuesCount " .
        "newColumns=$newColumnsCount " .
        "newValues=$newValuesCount\n"
    );
    exit(4);
}

$data = str_replace($oldColumns, $newColumns, $data, $countColumns);
$data = str_replace($oldValues, $newValues, $data, $countValues);

if ($countColumns !== 2 || $countValues !== 2) {
    fwrite(STDERR, "Numero sostituzioni errato\n");
    exit(5);
}

if (file_put_contents($file, $data) === false) {
    fwrite(STDERR, "Impossibile scrivere il file\n");
    exit(6);
}

echo "PATCH_APPLICATA columns=$countColumns values=$countValues\n";
PHP

section "Applicazione patch"
PATCH_RESULT="$("$PHP_BIN" "$PATCHER" "$TARGET_FILE")"
echo "$PATCH_RESULT" | tee -a "$REPORT"

if [[ "$PATCH_RESULT" == PATCH_APPLICATA* ]]; then
    PATCH_ACTIVE=1
fi

section "Verifica statica del controller"
{
    "$PHP_BIN" -l "$TARGET_FILE"

    NEW_COLUMNS_COUNT="$(
        grep -F -o \
        '`cal_special_op`,`cal_act`,`cal_url`,`cal_category`,`cal_repeat_type`,`cal_repeat_id`,`cal_all_turn`' \
        "$TARGET_FILE" | wc -l
    )"

    NEW_VALUES_COUNT="$(
        grep -F -o \
        "'\$specialop','0','','','','0','0'" \
        "$TARGET_FILE" | wc -l
    )"

    echo "Blocchi colonne corretti: $NEW_COLUMNS_COUNT"
    echo "Blocchi valori corretti: $NEW_VALUES_COUNT"

    [[ "$NEW_COLUMNS_COUNT" -eq 2 ]]
    [[ "$NEW_VALUES_COUNT" -eq 2 ]]
} >> "$REPORT" 2>&1

cp -a -- "$TARGET_FILE" "$PATCHED_COPY"

section "Reload runtime"
reload_runtime >> "$REPORT" 2>&1

section "Test HTTP locale"
if [[ "$ALLOW_TEST" == "1" ]]; then
    echo "Test HTTP saltato in ambiente simulato." >> "$REPORT"
elif command -v curl >/dev/null 2>&1; then
    HTTP_CODE="$(
        curl -sS --max-time 20 \
            -H "Host: $TARGET_HOST" \
            -o "$WORKDIR/http_body.txt" \
            -w '%{http_code}' \
            "http://127.0.0.1/ax_calendar.php"
    )"
    echo "HTTP_CODE=$HTTP_CODE" >> "$REPORT"
    [[ "$HTTP_CODE" =~ ^(200|302)$ ]]
else
    echo "curl non disponibile." >> "$REPORT"
fi

section "Esito"
{
    echo "PATCH: $PATCH_RESULT"
    echo "PHP LINT: OK"
    echo "DUE INSERT CORRETTI: OK"
    echo "TEST SQL TEMPORARY: OK"
    echo "DATI REALI INSERITI: NO"
    echo "FILE MODIFICATO: $TARGET_FILE"
    echo "ESITO: COMPLETATO"
} >> "$REPORT"

(
    cd "$WORKDIR"
    zip -q -9 "$OUTPUT_ZIP" \
        "$(basename "$REPORT")" \
        "$(basename "$BACKUP_FILE")" \
        "$(basename "$PATCHED_COPY")" \
        "$(basename "$SCHEMA_OUTPUT")" \
        "$(basename "$SQL_TEST")" \
        "$(basename "$SQL_OUTPUT")"
)

zip -T "$OUTPUT_ZIP" >/dev/null

PATCH_ACTIVE=0

echo "STEP043C COMPLETATO"
echo "ZIP: $OUTPUT_ZIP"