#!/usr/bin/env bash
# STEP050 - Porting Presenze 215.6
#
# Audit della causa reale delle stampe Silvelox:
# le ore lavorate compaiono nella colonna ASS nonostante le causali ORD.
#
# Verifica:
# - utente AGOSTINI LORENZO / badge 000000038 nel JSON attivo;
# - valori time_day del JSON;
# - valori reali di ax_new_time_day;
# - somme per tipo in ax_new_time_causali;
# - giorni con timbrature ma ORD=0 e ABSE>0;
# - codice che costruisce e salva ORD/ABSE.
#
# Modalita': AUDIT ONLY.
# Nessuna modifica a file, database o servizi.
#
# Server atteso: 215.6 / ax-accessi

set -Eeuo pipefail
umask 077

STEP_NAME="STEP050_2156_SILVELOX_PRINT_ORD_AS_ABSE_DATA_FLOW_AUDIT_ONLY_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_DB="${TARGET_DB:-presenze_silvelox}"

ACTIVE_JSON="${ACTIVE_JSON:-$TARGET_ROOT/json_data/dataReportTable.json}"

TIME_HELPER="${TIME_HELPER:-$TARGET_ROOT/components/templates/custom_templates/ax_attendance/ax_schede_presenze_modA/timeCtrl/timeDataHelper.php}"
TIME_HELPER_OK="${TIME_HELPER_OK:-$TARGET_ROOT/components/templates/custom_templates/ax_attendance/ax_schede_presenze_modA/timeCtrl_silvelox_ok/timeDataHelper.php}"
TIME_HELPER_LAST="${TIME_HELPER_LAST:-$TARGET_ROOT/components/templates/custom_templates/ax_attendance/ax_schede_presenze_modA/timeCtrl_lastonsilvelox/timeDataHelper.php}"

BADGE="${BADGE:-000000038}"
LAST_NAME="${LAST_NAME:-AGOSTINI}"
FIRST_NAME="${FIRST_NAME:-LORENZO}"
PERIOD="${PERIOD:-2026-07}"

ALLOW_TEST="${ALLOW_TEST:-0}"
MYSQL_BIN_OVERRIDE="${MYSQL_BIN_OVERRIDE:-}"

STAMP="$(date +%Y%m%d_%H%M%S)"
OUTPUT_ZIP="$PWD/${STEP_NAME}_${STAMP}.zip"
WORKDIR="$(mktemp -d "$PWD/.${STEP_NAME}.XXXXXX")"

REPORT="$WORKDIR/${STEP_NAME}_REPORT.txt"
RESOLVER="$WORKDIR/resolve_and_compare.php"

IDENTITY="$WORKDIR/resolved_identity.tsv"
JSON_DAY="$WORKDIR/json_time_day.tsv"
JSON_CAUS="$WORKDIR/json_causali_day.tsv"
JSON_LINES="$WORKDIR/json_time_lines.tsv"

DB_DAY="$WORKDIR/db_ax_new_time_day.tsv"
DB_CAUS="$WORKDIR/db_ax_new_time_causali.tsv"
DB_SCHEMA="$WORKDIR/db_schema.txt"

COMPARE="$WORKDIR/day_comparison.tsv"
SUMMARY="$WORKDIR/automatic_findings.txt"
SOURCE_SNIPPETS="$WORKDIR/source_snippets.txt"
SOURCE_COMPARE="$WORKDIR/source_compare.txt"
SQL_FILE="$WORKDIR/audit_queries.sql"

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

on_error() {
    local rc=$?
    local line="${1:-?}"

    trap - ERR
    printf 'ERRORE linea %s, codice %s\n' "$line" "$rc" >&2
    exit "$rc"
}

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

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

progress() {
    printf '%s\n' "$1"
    printf '\n[%s] %s\n' "$(date '+%H:%M:%S')" "$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; atteso 215.6 / ax-accessi." >&2
        exit 10
    fi
fi

for required in "$ACTIVE_JSON" "$TIME_HELPER"; do
    [[ -f "$required" ]] || {
        echo "ERRORE: file richiesto non trovato: $required" >&2
        exit 11
    }
done

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
}

if ! [[ "$PERIOD" =~ ^[0-9]{4}-[0-9]{2}$ ]]; then
    echo "ERRORE: periodo non valido: $PERIOD" >&2
    exit 14
fi

YEAR="${PERIOD%-*}"
MONTH="${PERIOD#*-}"
DATE_FROM="${PERIOD}-01"
DATE_TO="$(
    "$PHP_BIN" -r '
        $d = new DateTime($argv[1] . "-01");
        $d->modify("last day of this month");
        echo $d->format("Y-m-d");
    ' "$PERIOD"
)"

{
    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 "DATABASE: $TARGET_DB"
    echo "JSON ATTIVO: $ACTIVE_JSON"
    echo "UTENTE ATTESO: $LAST_NAME $FIRST_NAME"
    echo "BADGE ATTESO: $BADGE"
    echo "PERIODO: $DATE_FROM -> $DATE_TO"
    echo "MODALITA': AUDIT ONLY"
    echo "NESSUNA MODIFICA ESEGUITA"
} > "$REPORT"

cat > "$RESOLVER" <<'PHP'
<?php

if ($argc !== 14) {
    fwrite(
        STDERR,
        "Uso: resolve_and_compare.php MODE JSON BADGE LAST FIRST PERIOD " .
        "IDENTITY JSON_DAY JSON_CAUS JSON_LINES DB_DAY DB_CAUS COMPARE\n"
    );
    exit(2);
}

list(
    ,
    $mode,
    $jsonFile,
    $badgeExpected,
    $lastExpected,
    $firstExpected,
    $period,
    $identityFile,
    $jsonDayFile,
    $jsonCausFile,
    $jsonLinesFile,
    $dbDayFile,
    $dbCausFile,
    $compareFile
) = $argv;

function normalizeText($value)
{
    $value = strtoupper(trim((string) $value));
    $value = preg_replace('/\s+/', ' ', $value);

    return $value;
}

function normalizeBadge($value)
{
    $digits = preg_replace('/[^0-9]/', '', (string) $value);

    return str_pad($digits, 9, '0', STR_PAD_LEFT);
}

function decimalValue($value)
{
    if ($value === null || $value === '') {
        return 0.0;
    }

    return (float) str_replace(',', '.', (string) $value);
}

function formatDecimal($value)
{
    return number_format((float) $value, 2, '.', '');
}

function readTsv($file)
{
    $rows = array();

    if (!is_file($file)) {
        return $rows;
    }

    $handle = fopen($file, 'rb');

    if ($handle === false) {
        return $rows;
    }

    while (($line = fgets($handle)) !== false) {
        $line = rtrim($line, "\r\n");

        if ($line === '') {
            continue;
        }

        $rows[] = explode("\t", $line);
    }

    fclose($handle);

    return $rows;
}

function writeTsv($file, $header, $rows)
{
    $handle = fopen($file, 'wb');

    if ($handle === false) {
        throw new Exception("Impossibile scrivere " . $file);
    }

    fwrite($handle, implode("\t", $header) . "\n");

    foreach ($rows as $row) {
        $out = array();

        foreach ($row as $value) {
            $value = str_replace(
                array("\r", "\n", "\t"),
                ' ',
                (string) $value
            );
            $out[] = $value;
        }

        fwrite($handle, implode("\t", $out) . "\n");
    }

    fclose($handle);
}

function resolveIdentity(
    $data,
    $badgeExpected,
    $lastExpected,
    $firstExpected
) {
    $badgeExpected = normalizeBadge($badgeExpected);
    $lastExpected = normalizeText($lastExpected);
    $firstExpected = normalizeText($firstExpected);

    $usersById = array();

    if (isset($data['dipendenti']) && is_array($data['dipendenti'])) {
        foreach ($data['dipendenti'] as $user) {
            if (!is_array($user) || !isset($user['usrId'])) {
                continue;
            }

            $usersById[(string) $user['usrId']] = $user;
        }
    }

    $candidates = array();

    if (isset($data['associazioni']) && is_array($data['associazioni'])) {
        foreach ($data['associazioni'] as $association) {
            if (!is_array($association) || !isset($association['usrId'])) {
                continue;
            }

            $userId = (string) $association['usrId'];
            $user = isset($usersById[$userId])
                ? $usersById[$userId]
                : array();

            $badge = isset($association['usrBdg'])
                ? normalizeBadge($association['usrBdg'])
                : '';

            $last = isset($user['usrLast'])
                ? normalizeText($user['usrLast'])
                : (
                    isset($association['usrLast'])
                        ? normalizeText($association['usrLast'])
                        : ''
                );

            $first = isset($user['usrFirst'])
                ? normalizeText($user['usrFirst'])
                : '';

            $score = 0;
            $reasons = array();

            if ($badge !== '' && $badge === $badgeExpected) {
                $score += 100;
                $reasons[] = 'badge';
            }

            if ($last !== '' && $last === $lastExpected) {
                $score += 20;
                $reasons[] = 'cognome';
            }

            if ($first !== '' && $first === $firstExpected) {
                $score += 20;
                $reasons[] = 'nome';
            }

            if ($score > 0) {
                $candidates[] = array(
                    'userId' => $userId,
                    'badge' => $badge,
                    'last' => $last,
                    'first' => $first,
                    'score' => $score,
                    'reason' => implode(',', $reasons)
                );
            }
        }
    }

    if (count($candidates) === 0) {
        throw new Exception(
            "Utente non trovato nel JSON attivo per badge/nome richiesti"
        );
    }

    usort(
        $candidates,
        function ($left, $right) {
            if ($left['score'] === $right['score']) {
                return strcmp($left['userId'], $right['userId']);
            }

            return $right['score'] - $left['score'];
        }
    );

    if (
        count($candidates) > 1 &&
        $candidates[0]['score'] === $candidates[1]['score'] &&
        $candidates[0]['userId'] !== $candidates[1]['userId']
    ) {
        throw new Exception(
            "Identita' ambigua nel JSON: piu' utenti con lo stesso punteggio"
        );
    }

    return $candidates[0];
}

function collectJsonData($data, $identity, $period)
{
    $userId = (string) $identity['userId'];
    $prefix = $period . '-';

    $days = array();
    $caus = array();
    $lines = array();

    if (isset($data['time_day']) && is_array($data['time_day'])) {
        foreach ($data['time_day'] as $row) {
            if (
                !is_array($row) ||
                (string) $row['userId'] !== $userId ||
                strpos((string) $row['day'], $prefix) !== 0
            ) {
                continue;
            }

            $day = (string) $row['day'];

            $days[$day] = array(
                'day' => $day,
                'ord' => decimalValue(
                    isset($row['ordinarie'])
                        ? $row['ordinarie']
                        : 0
                ),
                'ext' => decimalValue(
                    isset($row['straordinarie'])
                        ? $row['straordinarie']
                        : 0
                ),
                'abse' => decimalValue(
                    isset($row['assenze'])
                        ? $row['assenze']
                        : 0
                ),
                'magg' => decimalValue(
                    isset($row['maggiorazioni'])
                        ? $row['maggiorazioni']
                        : 0
                ),
                'over' => decimalValue(
                    isset($row['overturno'])
                        ? $row['overturno']
                        : 0
                ),
                'bank' => decimalValue(
                    isset($row['bancaore'])
                        ? $row['bancaore']
                        : 0
                ),
                'totLav' => decimalValue(
                    isset($row['totLav'])
                        ? $row['totLav']
                        : 0
                ),
                'workday' => isset($row['workday'])
                    ? (string) $row['workday']
                    : ''
            );
        }
    }

    if (isset($data['cauys_day']) && is_array($data['cauys_day'])) {
        foreach ($data['cauys_day'] as $row) {
            if (
                !is_array($row) ||
                (string) $row['userId'] !== $userId ||
                strpos((string) $row['dayCaus'], $prefix) !== 0
            ) {
                continue;
            }

            $day = (string) $row['dayCaus'];
            $type = strtoupper((string) $row['causType']);
            $gest = isset($row['causGest'])
                ? (string) $row['causGest']
                : '';

            if (!isset($caus[$day])) {
                $caus[$day] = array(
                    'ORD' => 0.0,
                    'EXT' => 0.0,
                    'ABSE' => 0.0,
                    'OVER' => 0.0,
                    'BANK' => 0.0,
                    'MAGG' => 0.0
                );
            }

            $value = decimalValue(
                isset($row['causTime'])
                    ? $row['causTime']
                    : 0
            );

            if ($gest === 'la_magg') {
                $caus[$day]['MAGG'] += $value;
            } elseif (isset($caus[$day][$type])) {
                $caus[$day][$type] += $value;
            }
        }
    }

    if (isset($data['time_lines']) && is_array($data['time_lines'])) {
        foreach ($data['time_lines'] as $row) {
            if (
                !is_array($row) ||
                (string) $row['user'] !== $userId ||
                strpos((string) $row['day'], $prefix) !== 0
            ) {
                continue;
            }

            $realCount = 0;
            $normCount = 0;

            if (
                isset($row['timbRealForCut']) &&
                is_array($row['timbRealForCut'])
            ) {
                $realCount = count($row['timbRealForCut']);
            }

            if (
                isset($row['timbNormForCut']) &&
                is_array($row['timbNormForCut'])
            ) {
                $normCount = count($row['timbNormForCut']);
            }

            $lines[(string) $row['day']] = array(
                'realCount' => $realCount,
                'normCount' => $normCount
            );
        }
    }

    ksort($days);
    ksort($caus);
    ksort($lines);

    return array($days, $caus, $lines);
}

if ($mode === 'extract') {
    $raw = file_get_contents($jsonFile);

    if ($raw === false) {
        throw new Exception("Impossibile leggere il JSON attivo");
    }

    $data = json_decode($raw, true);

    if (!is_array($data)) {
        throw new Exception(
            "JSON attivo non valido: " . json_last_error_msg()
        );
    }

    $identity = resolveIdentity(
        $data,
        $badgeExpected,
        $lastExpected,
        $firstExpected
    );

    list($days, $caus, $lines) = collectJsonData(
        $data,
        $identity,
        $period
    );

    if (count($days) === 0) {
        throw new Exception(
            "Nessuna riga time_day nel periodo per userId " .
            $identity['userId']
        );
    }

    writeTsv(
        $identityFile,
        array(
            'userId',
            'badge',
            'last',
            'first',
            'score',
            'reason'
        ),
        array(
            array(
                $identity['userId'],
                $identity['badge'],
                $identity['last'],
                $identity['first'],
                $identity['score'],
                $identity['reason']
            )
        )
    );

    $dayRows = array();

    foreach ($days as $row) {
        $dayRows[] = array(
            $row['day'],
            formatDecimal($row['ord']),
            formatDecimal($row['ext']),
            formatDecimal($row['abse']),
            formatDecimal($row['magg']),
            formatDecimal($row['over']),
            formatDecimal($row['bank']),
            formatDecimal($row['totLav']),
            $row['workday']
        );
    }

    writeTsv(
        $jsonDayFile,
        array(
            'day',
            'ordinarie',
            'straordinarie',
            'assenze',
            'maggiorazioni',
            'overturno',
            'bancaore',
            'totLav',
            'workday'
        ),
        $dayRows
    );

    $causRows = array();

    foreach ($caus as $day => $row) {
        $causRows[] = array(
            $day,
            formatDecimal($row['ORD']),
            formatDecimal($row['EXT']),
            formatDecimal($row['ABSE']),
            formatDecimal($row['MAGG']),
            formatDecimal($row['OVER']),
            formatDecimal($row['BANK'])
        );
    }

    writeTsv(
        $jsonCausFile,
        array(
            'day',
            'ORD',
            'EXT',
            'ABSE',
            'MAGG',
            'OVER',
            'BANK'
        ),
        $causRows
    );

    $lineRows = array();

    foreach ($lines as $day => $row) {
        $lineRows[] = array(
            $day,
            $row['realCount'],
            $row['normCount']
        );
    }

    writeTsv(
        $jsonLinesFile,
        array('day', 'real_clockings', 'normalized_clockings'),
        $lineRows
    );

    echo $identity['userId'] . "\n";
    exit(0);
}

if ($mode !== 'compare') {
    throw new Exception("Modalita' non valida");
}

$identityRows = readTsv($identityFile);

if (count($identityRows) < 2) {
    throw new Exception("Identita' non disponibile");
}

$userId = $identityRows[1][0];

$jsonDayRows = readTsv($jsonDayFile);
$jsonCausRows = readTsv($jsonCausFile);
$jsonLineRows = readTsv($jsonLinesFile);
$dbDayRows = readTsv($dbDayFile);
$dbCausRows = readTsv($dbCausFile);

$jsonDays = array();
$jsonCaus = array();
$jsonLines = array();
$dbDays = array();
$dbCaus = array();

for ($index = 1; $index < count($jsonDayRows); $index++) {
    $row = $jsonDayRows[$index];

    if (count($row) < 9) {
        continue;
    }

    $jsonDays[$row[0]] = array(
        'ORD' => decimalValue($row[1]),
        'EXT' => decimalValue($row[2]),
        'ABSE' => decimalValue($row[3]),
        'MAGG' => decimalValue($row[4]),
        'OVER' => decimalValue($row[5]),
        'BANK' => decimalValue($row[6]),
        'TOT' => decimalValue($row[7]),
        'WORKDAY' => $row[8]
    );
}

for ($index = 1; $index < count($jsonCausRows); $index++) {
    $row = $jsonCausRows[$index];

    if (count($row) < 7) {
        continue;
    }

    $jsonCaus[$row[0]] = array(
        'ORD' => decimalValue($row[1]),
        'EXT' => decimalValue($row[2]),
        'ABSE' => decimalValue($row[3]),
        'MAGG' => decimalValue($row[4]),
        'OVER' => decimalValue($row[5]),
        'BANK' => decimalValue($row[6])
    );
}

for ($index = 1; $index < count($jsonLineRows); $index++) {
    $row = $jsonLineRows[$index];

    if (count($row) < 3) {
        continue;
    }

    $jsonLines[$row[0]] = array(
        'REAL' => (int) $row[1],
        'NORM' => (int) $row[2]
    );
}

foreach ($dbDayRows as $row) {
    if (count($row) < 9) {
        continue;
    }

    $dbDays[$row[0]] = array(
        'ORD' => decimalValue($row[1]),
        'EXT' => decimalValue($row[2]),
        'ABSE' => decimalValue($row[3]),
        'MAGG' => decimalValue($row[4]),
        'OVER' => decimalValue($row[5]),
        'BANK' => decimalValue($row[6]),
        'WORKDAY' => $row[7],
        'DATA' => $row[8]
    );
}

foreach ($dbCausRows as $row) {
    if (count($row) < 4) {
        continue;
    }

    $day = $row[0];
    $type = strtoupper($row[1]);
    $gest = $row[2];
    $value = decimalValue($row[3]);

    if (!isset($dbCaus[$day])) {
        $dbCaus[$day] = array(
            'ORD' => 0.0,
            'EXT' => 0.0,
            'ABSE' => 0.0,
            'MAGG' => 0.0,
            'OVER' => 0.0,
            'BANK' => 0.0
        );
    }

    if ($gest === 'la_magg') {
        $dbCaus[$day]['MAGG'] += $value;
    } elseif (isset($dbCaus[$day][$type])) {
        $dbCaus[$day][$type] += $value;
    }
}

$allDays = array_unique(
    array_merge(
        array_keys($jsonDays),
        array_keys($jsonCaus),
        array_keys($jsonLines),
        array_keys($dbDays),
        array_keys($dbCaus)
    )
);

sort($allDays);

$outputRows = array();
$stats = array(
    'days' => 0,
    'json_db_day_mismatch' => 0,
    'db_day_vs_caus_mismatch' => 0,
    'worked_as_absence' => 0,
    'caus_ord_but_day_abse' => 0
);

foreach ($allDays as $day) {
    $stats['days']++;

    $jd = isset($jsonDays[$day])
        ? $jsonDays[$day]
        : array(
            'ORD' => 0,
            'EXT' => 0,
            'ABSE' => 0,
            'MAGG' => 0,
            'OVER' => 0,
            'BANK' => 0,
            'TOT' => 0,
            'WORKDAY' => ''
        );

    $jc = isset($jsonCaus[$day])
        ? $jsonCaus[$day]
        : array(
            'ORD' => 0,
            'EXT' => 0,
            'ABSE' => 0,
            'MAGG' => 0,
            'OVER' => 0,
            'BANK' => 0
        );

    $jl = isset($jsonLines[$day])
        ? $jsonLines[$day]
        : array('REAL' => 0, 'NORM' => 0);

    $dd = isset($dbDays[$day])
        ? $dbDays[$day]
        : array(
            'ORD' => 0,
            'EXT' => 0,
            'ABSE' => 0,
            'MAGG' => 0,
            'OVER' => 0,
            'BANK' => 0,
            'WORKDAY' => '',
            'DATA' => ''
        );

    $dc = isset($dbCaus[$day])
        ? $dbCaus[$day]
        : array(
            'ORD' => 0,
            'EXT' => 0,
            'ABSE' => 0,
            'MAGG' => 0,
            'OVER' => 0,
            'BANK' => 0
        );

    $flags = array();

    foreach (array('ORD', 'EXT', 'ABSE', 'MAGG', 'OVER', 'BANK') as $type) {
        if (abs($jd[$type] - $dd[$type]) > 0.001) {
            $flags['JSON_DB_DAY'] = true;
        }

        if (abs($dd[$type] - $dc[$type]) > 0.001) {
            $flags['DB_DAY_CAUS'] = true;
        }
    }

    if (isset($flags['JSON_DB_DAY'])) {
        $stats['json_db_day_mismatch']++;
    }

    if (isset($flags['DB_DAY_CAUS'])) {
        $stats['db_day_vs_caus_mismatch']++;
    }

    if (
        ($jl['REAL'] >= 2 || $jl['NORM'] >= 2) &&
        $dd['ORD'] <= 0.001 &&
        $dd['ABSE'] > 0.001
    ) {
        $flags['WORKED_AS_ABSENCE'] = true;
        $stats['worked_as_absence']++;
    }

    if (
        $dc['ORD'] > 0.001 &&
        $dd['ORD'] <= 0.001 &&
        $dd['ABSE'] > 0.001
    ) {
        $flags['CAUS_ORD_DAY_ABSE'] = true;
        $stats['caus_ord_but_day_abse']++;
    }

    $outputRows[] = array(
        $day,
        $userId,
        $jl['REAL'],
        $jl['NORM'],
        formatDecimal($jd['ORD']),
        formatDecimal($jd['ABSE']),
        formatDecimal($dd['ORD']),
        formatDecimal($dd['ABSE']),
        formatDecimal($dc['ORD']),
        formatDecimal($dc['ABSE']),
        implode(',', array_keys($flags))
    );
}

writeTsv(
    $compareFile,
    array(
        'day',
        'userId',
        'real_clockings',
        'normalized_clockings',
        'json_day_ORD',
        'json_day_ABSE',
        'db_day_ORD',
        'db_day_ABSE',
        'db_caus_ORD',
        'db_caus_ABSE',
        'flags'
    ),
    $outputRows
);

echo 'DAYS=' . $stats['days'] . "\n";
echo 'JSON_DB_DAY_MISMATCH=' .
    $stats['json_db_day_mismatch'] . "\n";
echo 'DB_DAY_VS_CAUS_MISMATCH=' .
    $stats['db_day_vs_caus_mismatch'] . "\n";
echo 'WORKED_AS_ABSENCE=' .
    $stats['worked_as_absence'] . "\n";
echo 'CAUS_ORD_BUT_DAY_ABSE=' .
    $stats['caus_ord_but_day_abse'] . "\n";
PHP

progress "[1/6] Risoluzione dell'utente nel JSON attivo"

USER_ID="$(
    "$PHP_BIN" "$RESOLVER" \
        extract \
        "$ACTIVE_JSON" \
        "$BADGE" \
        "$LAST_NAME" \
        "$FIRST_NAME" \
        "$PERIOD" \
        "$IDENTITY" \
        "$JSON_DAY" \
        "$JSON_CAUS" \
        "$JSON_LINES" \
        "$DB_DAY" \
        "$DB_CAUS" \
        "$COMPARE"
)"

[[ "$USER_ID" =~ ^[0-9]+$ ]] || {
    echo "ERRORE: userId non valido risolto dal JSON: $USER_ID" >&2
    exit 20
}

{
    echo "USER_ID RISOLTO: $USER_ID"
    cat "$IDENTITY"
    echo
    echo "===== JSON TIME_DAY ====="
    cat "$JSON_DAY"
    echo
    echo "===== JSON CAUSALI GIORNALIERE ====="
    cat "$JSON_CAUS"
    echo
    echo "===== JSON TIMBRATURE ====="
    cat "$JSON_LINES"
} >> "$REPORT"

progress "[2/6] Lettura delle tabelle reali ax_new_time_day e ax_new_time_causali"

cat > "$SQL_FILE" <<SQL
SELECT
    DATE_FORMAT(\`day\`, '%Y-%m-%d'),
    COALESCE(\`ordinarie\`, 0),
    COALESCE(\`straordinarie\`, 0),
    COALESCE(\`assenze\`, 0),
    COALESCE(\`maggiorazioni\`, 0),
    COALESCE(\`overturno\`, 0),
    COALESCE(\`bancaore\`, 0),
    COALESCE(\`workday\`, 0),
    COALESCE(\`data\`, '')
FROM \`${TARGET_DB}\`.\`ax_new_time_day\`
WHERE \`userId\` = ${USER_ID}
  AND \`day\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}'
ORDER BY \`day\`;

SELECT
    DATE_FORMAT(\`dayCaus\`, '%Y-%m-%d'),
    COALESCE(\`causType\`, ''),
    COALESCE(\`causGest\`, ''),
    SUM(COALESCE(\`causTime\`, 0))
FROM \`${TARGET_DB}\`.\`ax_new_time_causali\`
WHERE \`userId\` = ${USER_ID}
  AND \`dayCaus\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}'
GROUP BY
    \`dayCaus\`,
    \`causType\`,
    \`causGest\`
ORDER BY
    \`dayCaus\`,
    \`causType\`,
    \`causGest\`;
SQL

if "$MYSQL_BIN" \
    --batch \
    --raw \
    --skip-column-names \
    -e "
        SELECT
            DATE_FORMAT(\`day\`, '%Y-%m-%d'),
            COALESCE(\`ordinarie\`, 0),
            COALESCE(\`straordinarie\`, 0),
            COALESCE(\`assenze\`, 0),
            COALESCE(\`maggiorazioni\`, 0),
            COALESCE(\`overturno\`, 0),
            COALESCE(\`bancaore\`, 0),
            COALESCE(\`workday\`, 0),
            COALESCE(\`data\`, '')
        FROM \`${TARGET_DB}\`.\`ax_new_time_day\`
        WHERE \`userId\` = ${USER_ID}
          AND \`day\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}'
        ORDER BY \`day\`;
    " > "$DB_DAY" 2>&1; then
    :
else
    rc=$?
    cat "$DB_DAY" >&2
    exit "$rc"
fi

if "$MYSQL_BIN" \
    --batch \
    --raw \
    --skip-column-names \
    -e "
        SELECT
            DATE_FORMAT(\`dayCaus\`, '%Y-%m-%d'),
            COALESCE(\`causType\`, ''),
            COALESCE(\`causGest\`, ''),
            SUM(COALESCE(\`causTime\`, 0))
        FROM \`${TARGET_DB}\`.\`ax_new_time_causali\`
        WHERE \`userId\` = ${USER_ID}
          AND \`dayCaus\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}'
        GROUP BY
            \`dayCaus\`,
            \`causType\`,
            \`causGest\`
        ORDER BY
            \`dayCaus\`,
            \`causType\`,
            \`causGest\`;
    " > "$DB_CAUS" 2>&1; then
    :
else
    rc=$?
    cat "$DB_CAUS" >&2
    exit "$rc"
fi

{
    "$MYSQL_BIN" --batch --raw -e \
        "SHOW CREATE TABLE \`${TARGET_DB}\`.\`ax_new_time_day\`;"

    "$MYSQL_BIN" --batch --raw -e \
        "SHOW CREATE TABLE \`${TARGET_DB}\`.\`ax_new_time_causali\`;"

    "$MYSQL_BIN" --batch --raw --skip-column-names -e "
        SELECT CONCAT(
            'DAY_ROWS=', COUNT(*),
            '|ORD=', FORMAT(SUM(COALESCE(\`ordinarie\`,0)),2),
            '|ABSE=', FORMAT(SUM(COALESCE(\`assenze\`,0)),2)
        )
        FROM \`${TARGET_DB}\`.\`ax_new_time_day\`
        WHERE \`userId\` = ${USER_ID}
          AND \`day\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}';

        SELECT CONCAT(
            'CAUS_ROWS=', COUNT(*),
            '|ORD=', FORMAT(
                SUM(
                    CASE
                        WHEN \`causType\`='ORD'
                         AND COALESCE(\`causGest\`,'')<>'la_magg'
                        THEN COALESCE(\`causTime\`,0)
                        ELSE 0
                    END
                ),
                2
            ),
            '|ABSE=', FORMAT(
                SUM(
                    CASE
                        WHEN \`causType\`='ABSE'
                        THEN COALESCE(\`causTime\`,0)
                        ELSE 0
                    END
                ),
                2
            )
        )
        FROM \`${TARGET_DB}\`.\`ax_new_time_causali\`
        WHERE \`userId\` = ${USER_ID}
          AND \`dayCaus\` BETWEEN '${DATE_FROM}' AND '${DATE_TO}';
    "
} > "$DB_SCHEMA" 2>&1

{
    echo "===== DB AX_NEW_TIME_DAY ====="
    cat "$DB_DAY"
    echo
    echo "===== DB AX_NEW_TIME_CAUSALI ====="
    cat "$DB_CAUS"
    echo
    echo "===== SCHEMA E TOTALI ====="
    cat "$DB_SCHEMA"
} >> "$REPORT"

progress "[3/6] Confronto automatico JSON, tabella giornaliera, causali e timbrature"

COMPARE_SUMMARY="$(
    "$PHP_BIN" "$RESOLVER" \
        compare \
        "$ACTIVE_JSON" \
        "$BADGE" \
        "$LAST_NAME" \
        "$FIRST_NAME" \
        "$PERIOD" \
        "$IDENTITY" \
        "$JSON_DAY" \
        "$JSON_CAUS" \
        "$JSON_LINES" \
        "$DB_DAY" \
        "$DB_CAUS" \
        "$COMPARE"
)"

{
    echo "$COMPARE_SUMMARY"
    echo
    cat "$COMPARE"
} >> "$REPORT"

progress "[4/6] Verifica del codice che salva ORD e ABSE"

{
    echo "===== FILE ATTIVO ====="
    stat -c '%A %a %U:%G %s %y %n' "$TIME_HELPER"
    sha256sum "$TIME_HELPER"
    "$PHP_BIN" -l "$TIME_HELPER"

    echo
    echo "===== FUNZIONE insertUpdateTimeMaster ====="

    START_LINE="$(
        grep -nF 'public function insertUpdateTimeMaster(' "$TIME_HELPER" \
            | head -n 1 \
            | cut -d: -f1
    )"

    [[ "$START_LINE" =~ ^[0-9]+$ ]]

    END_LINE="$((START_LINE + 145))"

    nl -ba "$TIME_HELPER" \
        | sed -n "${START_LINE},${END_LINE}p"

    echo
    echo "===== OCCORRENZE CHIAVE ====="

    grep -nE \
        "outTypeDay|outType\\[|ordinarie|assenze|ax_new_time_day|causTime" \
        "$TIME_HELPER" \
        | head -n 500
} > "$SOURCE_SNIPPETS" 2>&1

{
    echo "===== CONFRONTO HASH ====="
    sha256sum "$TIME_HELPER"

    if [[ -f "$TIME_HELPER_OK" ]]; then
        sha256sum "$TIME_HELPER_OK"
        echo
        echo "===== DIFF ATTIVO vs timeCtrl_silvelox_ok ====="
        diff -u "$TIME_HELPER_OK" "$TIME_HELPER" \
            | head -n 2000 || true
    else
        echo "ASSENTE: $TIME_HELPER_OK"
    fi

    if [[ -f "$TIME_HELPER_LAST" ]]; then
        sha256sum "$TIME_HELPER_LAST"
        echo
        echo "===== DIFF ATTIVO vs timeCtrl_lastonsilvelox ====="
        diff -u "$TIME_HELPER_LAST" "$TIME_HELPER" \
            | head -n 2000 || true
    else
        echo "ASSENTE: $TIME_HELPER_LAST"
    fi
} > "$SOURCE_COMPARE" 2>&1

cat "$SOURCE_SNIPPETS" >> "$REPORT"
cat "$SOURCE_COMPARE" >> "$REPORT"

progress "[5/6] Classificazione della causa"

get_value() {
    local key="$1"
    printf '%s\n' "$COMPARE_SUMMARY" \
        | awk -F= -v key="$key" '$1 == key {print $2}'
}

JSON_DB_DAY_MISMATCH="$(get_value JSON_DB_DAY_MISMATCH)"
DB_DAY_VS_CAUS_MISMATCH="$(get_value DB_DAY_VS_CAUS_MISMATCH)"
WORKED_AS_ABSENCE="$(get_value WORKED_AS_ABSENCE)"
CAUS_ORD_BUT_DAY_ABSE="$(get_value CAUS_ORD_BUT_DAY_ABSE)"

JSON_DB_DAY_MISMATCH="${JSON_DB_DAY_MISMATCH:-0}"
DB_DAY_VS_CAUS_MISMATCH="${DB_DAY_VS_CAUS_MISMATCH:-0}"
WORKED_AS_ABSENCE="${WORKED_AS_ABSENCE:-0}"
CAUS_ORD_BUT_DAY_ABSE="${CAUS_ORD_BUT_DAY_ABSE:-0}"

CAUSE="NON_CLASSIFICATA"

if [[ "$JSON_DB_DAY_MISMATCH" -eq 0 && "$DB_DAY_VS_CAUS_MISMATCH" -gt 0 ]]; then
    CAUSE="AX_NEW_TIME_DAY_GIA_ERRATA_PRIMA_DELLA_STAMPA"
elif [[ "$JSON_DB_DAY_MISMATCH" -gt 0 ]]; then
    CAUSE="JSON_NON_COINCIDE_CON_AX_NEW_TIME_DAY"
elif [[ "$DB_DAY_VS_CAUS_MISMATCH" -eq 0 ]]; then
    CAUSE="DATI_GIORNALIERI_E_CAUSALI_COINCIDENTI"
fi

{
    echo "USER_ID=$USER_ID"
    echo "PERIODO=$PERIOD"
    echo "JSON_DB_DAY_MISMATCH=$JSON_DB_DAY_MISMATCH"
    echo "DB_DAY_VS_CAUS_MISMATCH=$DB_DAY_VS_CAUS_MISMATCH"
    echo "WORKED_AS_ABSENCE=$WORKED_AS_ABSENCE"
    echo "CAUS_ORD_BUT_DAY_ABSE=$CAUS_ORD_BUT_DAY_ABSE"
    echo "CAUSE=$CAUSE"
    echo
    echo "Interpretazione:"
    echo "- JSON_DB_DAY_MISMATCH=0 significa che Stimulsoft riceve gli stessi valori della tabella."
    echo "- DB_DAY_VS_CAUS_MISMATCH>0 significa che ax_new_time_day non riflette le causali corrette."
    echo "- WORKED_AS_ABSENCE>0 identifica giorni con timbrature ma ore registrate in ASS."
    echo "- CAUS_ORD_BUT_DAY_ABSE>0 identifica ORD nelle causali ma ABSE nella tabella giornaliera."
    echo
    echo "ESITO: AUDIT COMPLETATO"
    echo "NESSUNA MODIFICA ESEGUITA"
} > "$SUMMARY"

cat "$SUMMARY" >> "$REPORT"

progress "[6/6] Creazione ZIP"

(
    cd "$WORKDIR"

    zip -q -9 "$OUTPUT_ZIP" \
        "$(basename "$REPORT")" \
        "$(basename "$IDENTITY")" \
        "$(basename "$JSON_DAY")" \
        "$(basename "$JSON_CAUS")" \
        "$(basename "$JSON_LINES")" \
        "$(basename "$DB_DAY")" \
        "$(basename "$DB_CAUS")" \
        "$(basename "$DB_SCHEMA")" \
        "$(basename "$COMPARE")" \
        "$(basename "$SOURCE_SNIPPETS")" \
        "$(basename "$SOURCE_COMPARE")" \
        "$(basename "$SUMMARY")" \
        "$(basename "$SQL_FILE")"
)

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

echo "STEP050 COMPLETATO"
echo "ZIP: $OUTPUT_ZIP"