<?php
// ============================================================
// migrate-cars.php — place in web/ folder, run ONCE, then DELETE
// Transforms car_old data → car + car_image tables
// ============================================================

defined('YII_DEBUG') or define('YII_DEBUG', false);
defined('YII_ENV')   or define('YII_ENV', 'prod');

require __DIR__ . '/../vendor/autoload.php';
require __DIR__ . '/../vendor/yiisoft/yii2/Yii.php';

$config = require __DIR__ . '/../config/web.php';
(new yii\web\Application($config));

$db = Yii::$app->db;

// ── Summary counters ──────────────────────────────────────────
$totalOld       = 0;
$totalMigrated  = 0;
$totalSkipped   = 0;
$totalImages    = 0;
$errors         = [];

echo '<style>
    body { font-family: monospace; padding: 20px; background: #1a1a1a; color: #eee; }
    .success { color: #4ade80; }
    .error   { color: #f87171; }
    .info    { color: #60a5fa; }
    .warn    { color: #fbbf24; }
    h2       { color: #fff; border-bottom: 1px solid #444; padding-bottom: 8px; }
    .row     { padding: 4px 0; border-bottom: 1px solid #333; }
</style>';

echo '<h2>🚗 Car Migration: car_old → car + car_image</h2>';

// ── Fetch all old cars ─────────────────────────────────────────
$oldCars = $db->createCommand('SELECT * FROM car_old ORDER BY id ASC')->queryAll();
$totalOld = count($oldCars);

echo "<p class='info'>Found <strong>{$totalOld}</strong> records in car_old. Starting migration...</p>";
echo '<hr style="border-color:#444">';

// ── Image column groups ────────────────────────────────────────
$imageGroups = [
    'auction'   => ['img_auc',  array_map(fn($i) => "img_auc_{$i}",  range(1, 19))],
    'warehouse' => ['img_war',  array_map(fn($i) => "img_war_{$i}",  range(1, 19))],
    'loading'   => ['img_loa',  array_map(fn($i) => "img_loa_{$i}",  range(1, 19))],
];

foreach ($oldCars as $old) {
    $oldId = $old['id'];

    // ── Skip if already migrated (check by lot + vin) ──────────
    $exists = $db->createCommand(
        'SELECT id FROM car WHERE lot = :lot AND vin = :vin LIMIT 1'
    )->bindValues([':lot' => $old['lot'], ':vin' => $old['vin']])->queryScalar();

    if ($exists) {
        echo "<div class='row warn'>⚠ Skipped car_old ID={$oldId} (lot={$old['lot']}) — already exists in car table (car ID={$exists})</div>";
        $totalSkipped++;
        continue;
    }

    $transaction = $db->beginTransaction();

    try {
        // ── Insert into car table ───────────────────────────────
        $db->createCommand()->insert('car', [
            'vin'            => $old['vin']            ?? '',
            'lot'            => $old['lot']            ?? '',
            'title'          => $old['title']          ?? '',
            'info'           => $old['info']           ?? '',
            'liner'          => $old['liner']          ?? '',
            'year'           => $old['year']           ?? '',
            'price'          => $old['price']          ?? 0,
            'status'         => $old['status']         ?? 'at_auction',
            'auction_id'     => $old['auction_id']     ?? 0,
            'warehouse_id'   => $old['warehouse_id']   ?? 0,
            'destination_id' => $old['destination_id'] ?? 0,
            'client_id'      => $old['client_id']      ?? 0,
            'user_id'        => $old['user_id']        ?? 0,
            'purchase_date'  => !empty($old['purchase_date'])  ? $old['purchase_date']  : null,
            'received_date'  => !empty($old['received_date'])  ? $old['received_date']  : null,
            'eta_date'       => !empty($old['eta_date'])       ? $old['eta_date']       : null,
            'shipping_line'  => $old['shipping_line']  ?? '',
            'container'      => $old['container']      ?? '',
            'note'           => $old['note']           ?? '',
            'created'        => !empty($old['created']) ? $old['created'] : date('Y-m-d H:i:s'),
        ])->execute();

        $newCarId = $db->getLastInsertID();
        $imgCount = 0;

        // ── Insert images ───────────────────────────────────────
        foreach ($imageGroups as $type => [$mainCol, $extraCols]) {
            // Main image (img_auc, img_war, img_loa)
            if (!empty($old[$mainCol])) {
                $db->createCommand()->insert('car_image', [
                    'car_id'     => $newCarId,
                    'image_path' => $old[$mainCol],
                    'type'       => $type,
                ])->execute();
                $imgCount++;
            }

            // Extra images (img_auc_1 ... img_auc_19 etc)
            foreach ($extraCols as $col) {
                if (!empty($old[$col])) {
                    $db->createCommand()->insert('car_image', [
                        'car_id'     => $newCarId,
                        'image_path' => $old[$col],
                        'type'       => $type,
                    ])->execute();
                    $imgCount++;
                }
            }
        }

        $transaction->commit();

        $totalMigrated++;
        $totalImages += $imgCount;

        echo "<div class='row success'>✅ Migrated car_old ID={$oldId} → car ID={$newCarId} | lot={$old['lot']} | {$imgCount} images</div>";

    } catch (\Exception $e) {
        $transaction->rollBack();
        $errors[] = "car_old ID={$oldId}: " . $e->getMessage();
        echo "<div class='row error'>❌ Failed car_old ID={$oldId}: " . htmlspecialchars($e->getMessage()) . "</div>";
    }
}

// ── Summary ───────────────────────────────────────────────────
echo '<hr style="border-color:#444">';
echo '<h2>📊 Migration Summary</h2>';
echo "<p class='info'>Total in car_old: <strong>{$totalOld}</strong></p>";
echo "<p class='success'>✅ Migrated: <strong>{$totalMigrated}</strong></p>";
echo "<p class='warn'>⚠ Skipped (already exist): <strong>{$totalSkipped}</strong></p>";
echo "<p class='info'>🖼 Total images created: <strong>{$totalImages}</strong></p>";

if (!empty($errors)) {
    echo "<p class='error'>❌ Errors: <strong>" . count($errors) . "</strong></p>";
    echo "<ul class='error'>";
    foreach ($errors as $err) {
        echo "<li>" . htmlspecialchars($err) . "</li>";
    }
    echo "</ul>";
} else {
    echo "<p class='success'>No errors! 🎉</p>";
}

echo '<hr style="border-color:#444">';
echo '<p style="color:#f87171; font-size:1.2em;"><strong>⚠️ DELETE this file (web/migrate-cars.php) immediately!</strong></p>';
