modulos_internal/contracts/save_stages.php

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);

date_default_timezone_set("Australia/Hobart");

$cfg = require __DIR__ . '/config.php';

$dsn = 'mysql:host=' . $cfg['db_host'] . ';dbname=' . $cfg['db_name'] . ';charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try {
    $pdo = new PDO($dsn, $cfg['db_username'], $cfg['db_password'], $options);
} catch (PDOException $e) {
    exit('Database connection failed: ' . $e->getMessage());
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    exit('Method Not Allowed');
}

$app_id          = (int)($_POST['application_id'] ?? 0);
$submission_date = trim($_POST['submission_date'] ?? '') ?: null;
$required_by     = trim($_POST['required_by'] ?? '') ?: null;
$stages          = $_POST['stages'] ?? [];

$paused  = isset($_POST['clock_paused']) ? 1 : 0;
$reason  = trim($_POST['clock_pause_reason'] ?? '');

$uploadDir = __DIR__ . "/uploads/app_$app_id";
if (!is_dir($uploadDir)) @mkdir($uploadDir, 0775, true);

$pdo->beginTransaction();

try {
    // --- Applications: single atomic update (dates + clock state)
    $cur = $pdo->prepare("SELECT clock_paused FROM applications WHERE id = ?");
    $cur->execute([$app_id]);
    $before = $cur->fetch(PDO::FETCH_ASSOC) ?: ['clock_paused' => 0];
    $wasPaused = (int)($before['clock_paused'] ?? 0);

    $sql = "
      UPDATE applications
         SET submission_date      = :submission,
             required_by          = :required_by,
             clock_paused         = :paused,
             clock_pause_reason   = :reason,
             clock_paused_at = CASE
               WHEN :paused = 1 AND :wasPaused = 0 THEN NOW()
               WHEN :paused = 0 THEN NULL
               ELSE clock_paused_at
             END
       WHERE id = :id
    ";
    $st = $pdo->prepare($sql);
    $st->execute([
        ':submission' => $submission_date,
        ':required_by'=> $required_by,
        ':paused'     => $paused,
        ':wasPaused'  => $wasPaused,
        ':reason'     => ($reason !== '' ? $reason : null),
        ':id'         => $app_id,
    ]);

    // --- Stage helpers
    $getRow = $pdo->prepare("SELECT id, pdf_path FROM application_stages WHERE id = ? AND application_id = ?");

    $ins = $pdo->prepare("
      INSERT INTO application_stages
        (application_id, position, title, description, status, stage_date, pdf_path, created_at, updated_at)
      VALUES
        (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
        title = VALUES(title),
        description = VALUES(description),
        status = VALUES(status),
        stage_date = VALUES(stage_date),
        pdf_path = COALESCE(VALUES(pdf_path), pdf_path),
        updated_at = NOW()
    ");

    $upd = $pdo->prepare("
      UPDATE application_stages
         SET title = ?, description = ?, status = ?, stage_date = ?, pdf_path = ?, updated_at = NOW()
       WHERE id = ? AND application_id = ?
    ");

    // --- Upsert all stages
    foreach ($stages as $i => $stage) {
        $id       = (int)($stage['id'] ?? 0);
        $position = isset($stage['position']) ? (int)$stage['position'] : $i;

        $title    = trim($stage['title'] ?? '');
        $status   = $stage['status'] ?? 'pending';
        $date     = $stage['date'] ?: null;
        $notes    = trim($stage['notes'] ?? '');

        // Auto-set date when marking complete with empty date
        if ($status === 'complete' && empty($date)) {
            $date = date('Y-m-d');
        }

        // File handling
        $newPdfPath = null;
        $removePdf  = !empty($stage['remove_pdf']);
        $existingPdf = null;

        if ($id > 0) {
            $getRow->execute([$id, $app_id]);
            if ($row = $getRow->fetch()) $existingPdf = $row['pdf_path'];
        }

        // New upload?
        if (isset($_FILES['stages']['error'][$i]['pdf']) && $_FILES['stages']['error'][$i]['pdf'] === UPLOAD_ERR_OK) {
            $originalName = basename($_FILES['stages']['name'][$i]['pdf']);
            $safeName     = date('Ymd_His') . '_' . preg_replace('/[^a-zA-Z0-9._-]/', '_', $originalName);
            $targetAbs    = $uploadDir . '/' . $safeName;
            if (move_uploaded_file($_FILES['stages']['tmp_name'][$i]['pdf'], $targetAbs)) {
                $newPdfPath = "uploads/app_$app_id/$safeName";
                if ($existingPdf && is_file(__DIR__ . '/' . $existingPdf)) @unlink(__DIR__ . '/' . $existingPdf);
            }
        } elseif ($removePdf && $existingPdf) {
            if (is_file(__DIR__ . '/' . $existingPdf)) @unlink(__DIR__ . '/' . $existingPdf);
            $existingPdf = null;
            $newPdfPath  = null; // explicit null keeps cleared
        }

        if ($id > 0) {
            $pdfToStore = $newPdfPath !== null ? $newPdfPath : $existingPdf; // keep unless replaced/removed
            $upd->execute([$title, $notes, $status, $date, $pdfToStore, $id, $app_id]);
        } else {
            $pdfForInsert = $newPdfPath; // may be NULL
            $ins->execute([$app_id, $position, $title, $notes, $status, $date, $pdfForInsert]);
        }
    }

    $pdo->commit();
    header("Location: edit_application.php?id=$app_id");
    exit;

} catch (Throwable $e) {
    $pdo->rollBack();
    http_response_code(500);
    exit('Save failed: ' . $e->getMessage());
}