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

# STEP013 - SERVER ATTESO: 215.6 / ax-accessi
#
# Completa i dati demo di presenze_distribuite.terminal_clockings:
# - usa come sorgente le timbrature DEMO di ingresso alle 08:05;
# - aggiunge l'uscita alle 17:05 dello stesso giorno;
# - non inserisce duplicati;
# - marca soltanto le nuove righe in dbgInfo;
# - in caso di verifica incoerente rimuove automaticamente solo le righe create.
#
# La tabella e' MyISAM: il rollback e' gestito tramite il marcatore univoco dbgInfo.
# Lascia nella cartella di lancio soltanto lo ZIP finale.

START_DIR="$PWD"
STAMP="$(date '+%Y%m%d_%H%M%S')"
BASE="STEP013_2156_DISTRIBUITE_TERMINAL_CLOCKINGS_DEMO_EXITS_PATCH_TEST_COMMIT_ZIP_ONLY_${STAMP}"
WORKDIR="${START_DIR}/.${BASE}_work_$$"
OUTZIP="${START_DIR}/${BASE}.zip"
LOGFILE="${WORKDIR}/run.log"
REPORT="${WORKDIR}/report.txt"
INSERT_SQL="${WORKDIR}/insert_demo_exits.sql"
ROLLBACK_SQL="${WORKDIR}/rollback.sql"
MARKER="${BASE}"
PACKAGED=0
INSERT_STARTED=0
ROLLBACK_DONE=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}"
SOURCE_TIME="${STEP_SOURCE_TIME:-08:05:00}"
EXIT_TIME="${STEP_EXIT_TIME:-17:05:00}"
TERMINAL_ID="${STEP_TERMINAL_ID:-00153900D001}"

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}, 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 "DATABASE: $TARGET_DB"
log "MODALITA': PATCH + TEST + COMMIT"
log "Ingresso demo: $SOURCE_TIME"
log "Uscita demo: $EXIT_TIME"
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 "Database: $TARGET_DB"
    echo "Modalita': PATCH + TEST + COMMIT"
    echo "Data: $(date --iso-8601=seconds)"
    echo "Hostname: $ACTUAL_HOST"
    echo "IP: $ACTUAL_IPS"
    echo "Ingresso demo: $SOURCE_TIME"
    echo "Uscita demo: $EXIT_TIME"
    echo "Terminale demo: $TERMINAL_ID"
    echo "Marcatore: $MARKER"
    echo
} >>"$REPORT"

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

[[ "$SOURCE_TIME" =~ ^([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$ ]] \
    || fail "orario ingresso non valido: $SOURCE_TIME"

[[ "$EXIT_TIME" =~ ^([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$ ]] \
    || fail "orario uscita non valido: $EXIT_TIME"

[[ "$SOURCE_TIME" < "$EXIT_TIME" ]] \
    || fail "l'orario di uscita deve essere successivo all'ingresso"

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

rollback_inserted() {
    local remaining

    log "Rollback delle sole righe create da $MARKER"

    if "${MYSQL[@]}" --show-warnings <"$ROLLBACK_SQL" >>"$LOGFILE" 2>&1; then
        remaining="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}';")"

        if [[ "$remaining" == "0" ]]; then
            ROLLBACK_DONE=1
            report "Rollback automatico completato."
            return 0
        fi
    fi

    fail "rollback automatico fallito o incompleto"
    return 1
}

log "[1/7] Controlli schema"

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

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

for table in terminal_clockings ax_associazioni_temporali ax_user; do
    EXISTS="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='${table}';")"

    [[ "$EXISTS" == "1" ]] || fail "tabella assente: ${TARGET_DB}.${table}"
done

for column in \
    Id Sense Type BadgeNumber Reason DateAndTime TerminalId \
    codeControll originalData infoData masterLock dbgInfo
do
    EXISTS="$(sql_scalar "
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings'
  AND COLUMN_NAME='${column}';")"

    [[ "$EXISTS" == "1" ]] || fail "colonna terminal_clockings.${column} assente"
done

ENGINE="$(sql_scalar "
SELECT ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='${TARGET_DB}'
  AND TABLE_NAME='terminal_clockings';")"

[[ "$ENGINE" == "MyISAM" ]] || warn "engine terminal_clockings=${ENGINE}; audit atteso MyISAM"

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

log "[2/7] Precontrolli dati demo"

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

SOURCE_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime;")"

SOURCE_BADGES="$(sql_scalar "
SELECT COUNT(DISTINCT s.BadgeNumber)
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime;")"

ACTIVE_SOURCE_BADGES="$(sql_scalar "
SELECT COUNT(DISTINCT s.BadgeNumber)
FROM \`${TARGET_DB}\`.terminal_clockings s
JOIN \`${TARGET_DB}\`.ax_associazioni_temporali a
  ON a.usrBdg=s.BadgeNumber
 AND a.attuale='1'
JOIN \`${TARGET_DB}\`.ax_user u
  ON u.usrId=a.usrId
 AND u.usrActive='1'
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime;")"

SOURCE_DUPLICATES="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT s.BadgeNumber,s.DateAndTime
    FROM \`${TARGET_DB}\`.terminal_clockings s
    WHERE s.infoData='DEMO'
      AND s.Sense='0'
      AND s.Type='1'
      AND s.Reason='0'
      AND TIME(s.DateAndTime)='${SOURCE_TIME}'
      AND s.TerminalId='${TERMINAL_ID}'
      AND s.originalData=s.DateAndTime
    GROUP BY s.BadgeNumber,s.DateAndTime
    HAVING COUNT(*)>1
) d;")"

CANDIDATES="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime
  AND NOT EXISTS (
      SELECT 1
      FROM \`${TARGET_DB}\`.terminal_clockings e
      WHERE e.BadgeNumber=s.BadgeNumber
        AND e.DateAndTime=TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}')
  );")"

PREVIOUS_MARKER_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}';")"

{
    printf 'controllo\tvalore\n'
    printf 'rows_before\t%s\n' "$ROWS_BEFORE"
    printf 'source_rows\t%s\n' "$SOURCE_ROWS"
    printf 'source_badges\t%s\n' "$SOURCE_BADGES"
    printf 'active_source_badges\t%s\n' "$ACTIVE_SOURCE_BADGES"
    printf 'source_duplicate_pairs\t%s\n' "$SOURCE_DUPLICATES"
    printf 'missing_exit_candidates\t%s\n' "$CANDIDATES"
    printf 'previous_marker_rows\t%s\n' "$PREVIOUS_MARKER_ROWS"
} >"${WORKDIR}/01_PRECHECKS.tsv"

[[ "$SOURCE_ROWS" -gt 0 ]] || fail "nessuna timbratura DEMO di ingresso trovata"
[[ "$SOURCE_BADGES" -gt 0 ]] || fail "nessun badge DEMO sorgente"
[[ "$ACTIVE_SOURCE_BADGES" == "$SOURCE_BADGES" ]] \
    || fail "badge sorgente attivi ${ACTIVE_SOURCE_BADGES}/${SOURCE_BADGES}"
[[ "$SOURCE_DUPLICATES" == "0" ]] || fail "ingressi DEMO duplicati: $SOURCE_DUPLICATES"
[[ "$PREVIOUS_MARKER_ROWS" == "0" ]] || fail "marcatore corrente gia' presente"

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

log "[3/7] Esportazione candidati"

"${MYSQL[@]}" -e "
SELECT
    s.Id AS source_Id,
    s.BadgeNumber,
    s.DateAndTime AS ingresso,
    TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}') AS uscita,
    s.TerminalId
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime
  AND NOT EXISTS (
      SELECT 1
      FROM \`${TARGET_DB}\`.terminal_clockings e
      WHERE e.BadgeNumber=s.BadgeNumber
        AND e.DateAndTime=TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}')
  )
ORDER BY s.BadgeNumber,s.DateAndTime;" \
    >"${WORKDIR}/02_CANDIDATES.tsv" 2>>"$LOGFILE"

cat >"$ROLLBACK_SQL" <<SQL
DELETE FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}';
SQL

if [[ "$CANDIDATES" == "0" ]]; then
    {
        echo "NESSUNA MODIFICA NECESSARIA"
        echo "Tutti i ${SOURCE_ROWS} ingressi DEMO hanno gia' la relativa uscita alle ${EXIT_TIME}."
        echo "Righe totali: ${ROWS_BEFORE}"
    } >"${WORKDIR}/99_SUMMARY.txt"

    report "Nessuna uscita mancante."
    log "NESSUNA MODIFICA NECESSARIA"
    exit 0
fi

log "[4/7] Generazione SQL di inserimento"

cat >"$INSERT_SQL" <<SQL
INSERT INTO \`${TARGET_DB}\`.terminal_clockings
(
    Sense,
    Type,
    BadgeNumber,
    Reason,
    DateAndTime,
    TerminalId,
    codeControll,
    originalData,
    infoData,
    masterLock,
    dbgInfo
)
SELECT
    '1',
    s.Type,
    s.BadgeNumber,
    s.Reason,
    TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}'),
    s.TerminalId,
    0,
    TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}'),
    'DEMO',
    0,
    '${MARKER}'
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime
  AND NOT EXISTS (
      SELECT 1
      FROM \`${TARGET_DB}\`.terminal_clockings e
      WHERE e.BadgeNumber=s.BadgeNumber
        AND e.DateAndTime=TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}')
  )
ORDER BY s.BadgeNumber,s.DateAndTime;
SQL

log "[5/7] Inserimento uscite DEMO"

INSERT_STARTED=1

if ! "${MYSQL[@]}" --show-warnings <"$INSERT_SQL" >>"$LOGFILE" 2>&1; then
    fail "inserimento SQL fallito"
    rollback_inserted || true
    exit 26
fi

log "[6/7] Verifica dopo inserimento"

INSERTED_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}';")"

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

MISSING_AFTER="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings s
WHERE s.infoData='DEMO'
  AND s.Sense='0'
  AND s.Type='1'
  AND s.Reason='0'
  AND TIME(s.DateAndTime)='${SOURCE_TIME}'
  AND s.TerminalId='${TERMINAL_ID}'
  AND s.originalData=s.DateAndTime
  AND NOT EXISTS (
      SELECT 1
      FROM \`${TARGET_DB}\`.terminal_clockings e
      WHERE e.BadgeNumber=s.BadgeNumber
        AND e.DateAndTime=TIMESTAMP(DATE(s.DateAndTime),'${EXIT_TIME}')
  );")"

BAD_INSERTED_ROWS="$(sql_scalar "
SELECT COUNT(*)
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}'
  AND (
      Sense<>'1'
      OR Type<>'1'
      OR Reason<>'0'
      OR TIME(DateAndTime)<>'${EXIT_TIME}'
      OR TerminalId<>'${TERMINAL_ID}'
      OR originalData<>DateAndTime
      OR infoData<>'DEMO'
      OR codeControll<>0
      OR masterLock<>0
  );")"

DUPLICATE_EXIT_PAIRS="$(sql_scalar "
SELECT COUNT(*)
FROM (
    SELECT BadgeNumber,DateAndTime
    FROM \`${TARGET_DB}\`.terminal_clockings
    WHERE TIME(DateAndTime)='${EXIT_TIME}'
      AND BadgeNumber IN (
          SELECT DISTINCT BadgeNumber
          FROM \`${TARGET_DB}\`.terminal_clockings
          WHERE infoData='DEMO'
            AND Sense='0'
            AND TIME(DateAndTime)='${SOURCE_TIME}'
      )
    GROUP BY BadgeNumber,DateAndTime
    HAVING COUNT(*)>1
) d;")"

EXPECTED_ROWS_AFTER=$((ROWS_BEFORE + CANDIDATES))

{
    printf 'controllo\tatteso\trilevato\n'
    printf 'inserted_rows\t%s\t%s\n' "$CANDIDATES" "$INSERTED_ROWS"
    printf 'rows_after\t%s\t%s\n' "$EXPECTED_ROWS_AFTER" "$ROWS_AFTER"
    printf 'missing_exits_after\t0\t%s\n' "$MISSING_AFTER"
    printf 'bad_inserted_rows\t0\t%s\n' "$BAD_INSERTED_ROWS"
    printf 'duplicate_exit_pairs\t0\t%s\n' "$DUPLICATE_EXIT_PAIRS"
} >"${WORKDIR}/03_POSTCHECKS.tsv"

VERIFY_OK=1
[[ "$INSERTED_ROWS" == "$CANDIDATES" ]] || VERIFY_OK=0
[[ "$ROWS_AFTER" == "$EXPECTED_ROWS_AFTER" ]] || VERIFY_OK=0
[[ "$MISSING_AFTER" == "0" ]] || VERIFY_OK=0
[[ "$BAD_INSERTED_ROWS" == "0" ]] || VERIFY_OK=0
[[ "$DUPLICATE_EXIT_PAIRS" == "0" ]] || VERIFY_OK=0

if [[ "$VERIFY_OK" -ne 1 ]]; then
    fail "verifica finale incoerente"
    rollback_inserted || true
    exit 27
fi

"${MYSQL[@]}" -e "
SELECT *
FROM \`${TARGET_DB}\`.terminal_clockings
WHERE dbgInfo='${MARKER}'
ORDER BY BadgeNumber,DateAndTime
LIMIT 100;" >"${WORKDIR}/04_INSERTED_SAMPLE.tsv" 2>>"$LOGFILE"

log "[7/7] Esito"

{
    echo "PATCH COMPLETATA"
    echo "Database: $TARGET_DB"
    echo "Ingressi DEMO sorgente: $SOURCE_ROWS"
    echo "Badge DEMO: $SOURCE_BADGES"
    echo "Uscite inserite alle ${EXIT_TIME}: $INSERTED_ROWS"
    echo "Righe prima: $ROWS_BEFORE"
    echo "Righe dopo: $ROWS_AFTER"
    echo "Uscite mancanti dopo il test: $MISSING_AFTER"
    echo "Duplicati uscita: $DUPLICATE_EXIT_PAIRS"
    echo "Rollback automatico: non necessario"
} >"${WORKDIR}/99_SUMMARY.txt"

report "Uscite DEMO inserite: $INSERTED_ROWS"
report "Righe finali terminal_clockings: $ROWS_AFTER"
report "Nessuna uscita mancante e nessun duplicato."

log "PATCH COMPLETATA E VERIFICATA"