<?php

if (PHP_SAPI !== 'cli') {
    http_response_code(403);
    exit("This maintenance script can only be run from the command line.\n");
}

require_once __DIR__ . '/classes/Database.php';

$options = getopt('', ['date::', 'apply', 'help']);
if (isset($options['help'])) {
    echo "Usage: php calibrate_shipment_unique_codes.php [--date=YYYY-MM-DD] [--apply]\n";
    echo "Without --apply the script only previews the proposed changes.\n";
    exit(0);
}

$transferDate = isset($options['date']) ? trim((string)$options['date']) : '2026-08-02';
$parsedDate = DateTimeImmutable::createFromFormat('!Y-m-d', $transferDate);
if (!$parsedDate || $parsedDate->format('Y-m-d') !== $transferDate) {
    fwrite(STDERR, "Invalid --date. Use YYYY-MM-DD.\n");
    exit(1);
}

$apply = isset($options['apply']);
$conn = (new Database())->getConnection();
if (!$conn instanceof PDO) {
    fwrite(STDERR, "Database connection failed.\n");
    exit(1);
}

// shipment_items contains the item ID, while item_tracking preserves the
// unique code that was actually placed on the shipment. Use the latest shipped
// tracking row for each shipment/item pair as the authoritative snapshot.
$sql = "
    SELECT
        s.shipment_id,
        s.shipment_date,
        s.status,
        s.to_branch_id,
        b.branch_name,
        i.item_id,
        i.current_location_id,
        i.unique_code AS current_unique_code,
        tracking.unique_code AS shipment_unique_code,
        conflict.item_id AS conflicting_item_id
    FROM shipments s
    JOIN shipment_items si ON si.shipment_id = s.shipment_id
    JOIN items i ON i.item_id = si.item_id
    JOIN (
        SELECT t.shipment_id, t.item_id, t.unique_code
        FROM item_tracking t
        JOIN (
            SELECT shipment_id, item_id, MAX(tracking_id) AS tracking_id
            FROM item_tracking
            WHERE event_type = 'shipped'
              AND shipment_id IS NOT NULL
            GROUP BY shipment_id, item_id
        ) latest ON latest.tracking_id = t.tracking_id
    ) tracking
      ON tracking.shipment_id = s.shipment_id
     AND tracking.item_id = i.item_id
    LEFT JOIN branches b ON b.branch_id = s.to_branch_id
    LEFT JOIN items conflict
      ON conflict.unique_code = tracking.unique_code
     AND conflict.item_id <> i.item_id
    WHERE DATE(s.shipment_date) = :transfer_date
    ORDER BY s.shipment_date, s.shipment_id, i.item_id
";

$stmt = $conn->prepare($sql);
$stmt->execute([':transfer_date' => $transferDate]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// If an item was transferred more than once that day, its last shipment is the
// relevant destination/code snapshot.
$latestByItem = [];
foreach ($rows as $row) {
    $latestByItem[(int)$row['item_id']] = $row;
}

$placeholderCodes = ['', 'NO CODE', 'N/A', 'NULL'];
$codeOwners = [];
foreach ($latestByItem as $row) {
    $code = strtoupper(trim((string)$row['shipment_unique_code']));
    if ($code !== '') {
        $codeOwners[$code][] = (int)$row['item_id'];
    }
}

$eligible = [];
$skipped = [];
foreach ($latestByItem as $row) {
    $itemId = (int)$row['item_id'];
    $shipmentCode = trim((string)$row['shipment_unique_code']);
    $normalizedCode = strtoupper($shipmentCode);
    $reason = null;

    if (in_array($normalizedCode, $placeholderCodes, true)) {
        $reason = 'placeholder shipment code';
    } elseif ((int)$row['current_location_id'] !== (int)$row['to_branch_id']) {
        $reason = 'item is not at the shipment destination branch';
    } elseif (!empty($row['conflicting_item_id'])) {
        $reason = 'shipment code belongs to item #' . (int)$row['conflicting_item_id'];
    } elseif (count($codeOwners[$normalizedCode] ?? []) > 1) {
        $reason = 'shipment code is assigned to multiple transferred items';
    } elseif ((string)$row['current_unique_code'] === $shipmentCode) {
        $reason = 'already matched';
    }

    if ($reason === null) {
        $eligible[$itemId] = $row;
    } else {
        $row['skip_reason'] = $reason;
        $skipped[$itemId] = $row;
    }
}

echo "Shipment unique-code calibration for {$transferDate}\n";
echo 'Shipment rows: ' . count($rows) . "\n";
echo 'Distinct items: ' . count($latestByItem) . "\n";
echo 'Eligible changes: ' . count($eligible) . "\n";
echo 'Skipped/already matched: ' . count($skipped) . "\n\n";

foreach ($eligible as $row) {
    printf(
        "Item %-8d branch %-5d shipment %-8d %-30s -> %s\n",
        (int)$row['item_id'],
        (int)$row['to_branch_id'],
        (int)$row['shipment_id'],
        (string)$row['current_unique_code'],
        (string)$row['shipment_unique_code']
    );
}

if (!$apply) {
    echo "\nPreview only. Re-run with --apply to save these changes.\n";
    exit(0);
}

if (!$eligible) {
    echo "\nNo eligible changes to apply.\n";
    exit(0);
}

try {
    $conn->exec("
        CREATE TABLE IF NOT EXISTS item_unique_code_shipment_calibration (
            calibration_date DATE NOT NULL,
            item_id INT NOT NULL,
            shipment_id INT NOT NULL,
            branch_id INT NOT NULL,
            old_unique_code VARCHAR(255) NOT NULL,
            shipment_unique_code VARCHAR(255) NOT NULL,
            backed_up_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (calibration_date, item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $conn->beginTransaction();

    $backup = $conn->prepare("
        INSERT IGNORE INTO item_unique_code_shipment_calibration
            (calibration_date, item_id, shipment_id, branch_id, old_unique_code, shipment_unique_code)
        VALUES (?, ?, ?, ?, ?, ?)
    ");
    $update = $conn->prepare("
        UPDATE items
        SET unique_code = :shipment_code
        WHERE item_id = :item_id
          AND current_location_id = :branch_id
          AND unique_code = :old_code
          AND NOT EXISTS (
              SELECT 1 FROM (SELECT item_id, unique_code FROM items) other
              WHERE other.unique_code = :collision_code
                AND other.item_id <> :collision_item_id
          )
    ");

    $updated = 0;
    foreach ($eligible as $row) {
        $backup->execute([
            $transferDate,
            (int)$row['item_id'],
            (int)$row['shipment_id'],
            (int)$row['to_branch_id'],
            (string)$row['current_unique_code'],
            (string)$row['shipment_unique_code'],
        ]);
        $update->execute([
            ':shipment_code' => (string)$row['shipment_unique_code'],
            ':item_id' => (int)$row['item_id'],
            ':branch_id' => (int)$row['to_branch_id'],
            ':old_code' => (string)$row['current_unique_code'],
            ':collision_code' => (string)$row['shipment_unique_code'],
            ':collision_item_id' => (int)$row['item_id'],
        ]);
        $updated += $update->rowCount();
    }

    $conn->commit();
    echo "\nApplied {$updated} unique-code change(s).\n";
    echo "Recovery snapshot: item_unique_code_shipment_calibration\n";
} catch (Throwable $e) {
    if ($conn->inTransaction()) {
        $conn->rollBack();
    }
    fwrite(STDERR, "\nCalibration failed and was rolled back: " . $e->getMessage() . "\n");
    exit(1);
}
