<?php
declare(strict_types=1);

namespace App\Console;

use Core\DB;
use PDO;

/**
 * Migrate the legacy rookpmgt_orch_view DB into orchview2.
 *
 * Idempotent — reruns update existing rows by legacy id where we keep a lookup.
 * Uses --dry to preview.
 *
 *   php bin/console legacy:migrate
 *   php bin/console legacy:migrate --dry
 *   php bin/console legacy:migrate --only=sites,assets
 *
 * Everything is written to tenant_id = 1 (Orchid).
 */
final class LegacyMigrate
{
    private bool $dry = false;
    private array $only = [];
    private PDO $legacy;
    /** Map legacy_id → orchview2_id per entity so children can reference parents. */
    private array $map = [
        'site' => [], 'asset_type' => [], 'asset' => [],
        'incident_type' => [], 'incident' => [], 'user' => [],
        'checklist' => [], 'checklist_item' => [], 'checklist_entry' => [],
        'reading_session' => [],
    ];
    private int $tenantId = 1;
    private int $sysUserId = 1;

    public function run(array $argv = []): int
    {
        foreach ($argv as $a) {
            if ($a === '--dry') $this->dry = true;
            elseif (str_starts_with($a, '--only=')) $this->only = explode(',', substr($a, 7));
        }

        try {
            $this->legacy = new PDO(
                'mysql:host=' . env('LEGACY_DB_HOST', 'localhost')
                . ';dbname=' . env('LEGACY_DB_NAME', 'rookpmgt_orch_view') . ';charset=utf8mb4',
                (string) env('LEGACY_DB_USER', 'root'),
                (string) env('LEGACY_DB_PASSWORD', ''),
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
            );
        } catch (\Throwable $e) {
            fwrite(STDERR, "Cannot connect to legacy DB: " . $e->getMessage() . "\n");
            return 2;
        }

        fwrite(STDOUT, ($this->dry ? "[DRY RUN] " : "") . "Legacy migration → tenant #{$this->tenantId}\n");

        $steps = [
            'users', 'sites', 'asset_types', 'assets',
            'incident_types', 'incidents',
            'checklists', 'checklist_items', 'checklist_entries', 'checklist_item_entries', 'checklist_attachments',
            'plant_readings', 'plant_reading_attachments',
        ];
        foreach ($steps as $s) {
            if ($this->only && !in_array($s, $this->only, true)) continue;
            $m = 'migrate' . str_replace('_', '', ucwords($s, '_'));
            $t0 = microtime(true);
            $n = $this->$m();
            $dt = round(microtime(true) - $t0, 2);
            fwrite(STDOUT, sprintf("  %-22s → %5d rows (%.2fs)\n", $s, $n, $dt));
        }
        return 0;
    }

    /** Legacy `users` → orchview2 `users`. Passwords carried as-is (already password_hash). */
    private function migrateUsers(): int
    {
        $rows = $this->legacy->query('SELECT * FROM users WHERE deleted = 0')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $roleCode = $this->guessRoleFromLegacy($r);
            $roleId = (int) DB::value('SELECT role_id FROM roles WHERE code = :c', ['c' => $roleCode]);
            $params = [
                'tenant_id' => $this->tenantId,
                'role_id'   => $roleId ?: null,
                'username'  => $r['username'] ?? ('legacy_' . $r['id']),
                'password'  => $r['password'] ?: password_hash(bin2hex(random_bytes(8)), PASSWORD_DEFAULT),
                'f_name'    => $r['f_name'] ?? null,
                'l_name'    => $r['l_name'] ?? null,
                'email'     => $r['email']  ?? null,
                'phone'     => $r['phone']  ?? null,
                'status'    => 'active',
            ];
            $existing = DB::value(
                'SELECT user_id FROM users WHERE tenant_id = :t AND username = :u',
                ['t' => $this->tenantId, 'u' => $params['username']]
            );
            if ($existing) { $this->map['user'][(int)$r['id']] = (int)$existing; continue; }
            if (!$this->dry) { $id = (int) DB::insert('users', $params); $this->map['user'][(int)$r['id']] = $id; }
            $n++;
        }
        return $n;
    }

    private function guessRoleFromLegacy(array $r): string
    {
        $g = strtolower((string) ($r['user_group_name'] ?? ''));
        return match (true) {
            str_contains($g, 'admin')      => 'admin',
            str_contains($g, 'client')     => 'client',
            str_contains($g, 'manager') || str_contains($g, 'executive') || str_contains($g, 'supervisor') => 'project_manager',
            str_contains($g, 'field') || str_contains($g, 'tech') => 'field_technician',
            default                        => 'field_technician',
        };
    }

    /** Legacy `my_bases` → orchview2 `sites`. */
    private function migrateSites(): int
    {
        $rows = $this->legacy->query('SELECT * FROM my_bases')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $params = [
                'tenant_id' => $this->tenantId,
                'name'      => $r['base_name'] ?: 'Site #' . $r['id'],
                'status'    => 'active',
            ];
            $existing = DB::value('SELECT site_id FROM sites WHERE tenant_id = :t AND name = :n',
                ['t' => $this->tenantId, 'n' => $params['name']]);
            if ($existing) { $this->map['site'][(int)$r['id']] = (int)$existing; continue; }
            if (!$this->dry) { $id = (int) DB::insert('sites', $params); $this->map['site'][(int)$r['id']] = $id; }
            $n++;
        }
        return $n;
    }

    /** Legacy `equipment_types` → orchview2 `asset_types`. */
    private function migrateAssetTypes(): int
    {
        $rows = $this->legacy->query('SELECT * FROM equipment_types')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $name = $r['equipment_type_name'] ?: 'Type #' . $r['c0d'];
            $code = strtolower(preg_replace('/[^a-z0-9]+/i', '_', $name));
            $existing = DB::value('SELECT asset_type_id FROM asset_types WHERE tenant_id = :t AND code = :c',
                ['t' => $this->tenantId, 'c' => $code]);
            if ($existing) { $this->map['asset_type'][(int)$r['c0d']] = (int)$existing; continue; }
            $params = [
                'tenant_id' => $this->tenantId,
                'code'      => $code,
                'name'      => $name,
                'status'    => 'active',
                'created_by'=> $this->sysUserId,
            ];
            if (!$this->dry) { $id = (int) DB::insert('asset_types', $params); $this->map['asset_type'][(int)$r['c0d']] = $id; }
            $n++;
        }
        return $n;
    }

    /** Legacy `equipments` → orchview2 `assets`. */
    private function migrateAssets(): int
    {
        $rows = $this->legacy->query('SELECT * FROM equipments')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $typeId = $this->map['asset_type'][(int)$r['equipment_type_id']] ?? null;
            if (!$typeId) continue;
            $existing = DB::value(
                'SELECT asset_id FROM assets WHERE tenant_id = :t AND name = :n AND asset_type_id = :at',
                ['t' => $this->tenantId, 'n' => $r['equipment_name'] ?: 'Asset #' . $r['c0d'], 'at' => $typeId]
            );
            if ($existing) { $this->map['asset'][(int)$r['c0d']] = (int)$existing; continue; }
            $params = [
                'tenant_id'    => $this->tenantId,
                'site_id'      => null,
                'asset_type_id'=> $typeId,
                'name'         => $r['equipment_name'] ?: 'Asset #' . $r['c0d'],
                'serial_number'=> $r['serial_number'] ?? null,
                'status'       => 'active',
                'created_by'   => $this->sysUserId,
            ];
            if (!$this->dry) { $id = (int) DB::insert('assets', $params); $this->map['asset'][(int)$r['c0d']] = $id; }
            $n++;
        }
        return $n;
    }

    /** Legacy `incidents_types` → orchview2 `incident_types`. */
    private function migrateIncidentTypes(): int
    {
        $rows = $this->legacy->query('SELECT * FROM incidents_types')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $name = $r['incident_name'] ?: 'Type #' . $r['c0d'];
            $code = strtolower(preg_replace('/[^a-z0-9]+/i', '_', $name));
            $existing = DB::value(
                'SELECT incident_type_id FROM incident_types WHERE tenant_id = :t AND category = "equipment" AND code = :c',
                ['t' => $this->tenantId, 'c' => $code]
            );
            if ($existing) { $this->map['incident_type'][(int)$r['c0d']] = (int)$existing; continue; }
            if (!$this->dry) {
                $id = (int) DB::insert('incident_types', [
                    'tenant_id' => $this->tenantId,
                    'category'  => 'equipment',
                    'code'      => $code,
                    'name'      => $name,
                ]);
                $this->map['incident_type'][(int)$r['c0d']] = $id;
            }
            $n++;
        }
        return $n;
    }

    /** Legacy `incidents` (+ `equipments_incidents` link) → orchview2 `incidents`. */
    private function migrateIncidents(): int
    {
        $rows = $this->legacy->query('SELECT * FROM incidents')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $typeId = $this->map['incident_type'][(int)$r['incident_type_id']] ?? null;
            $assetId = null;
            $link = $this->legacy->prepare('SELECT equipment_id FROM equipments_incidents WHERE incident_id = :i LIMIT 1');
            $link->execute(['i' => $r['c0d']]);
            $legacyEqId = (int) ($link->fetchColumn() ?: 0);
            if ($legacyEqId) $assetId = $this->map['asset'][$legacyEqId] ?? null;

            $existing = DB::value(
                'SELECT incident_id FROM incidents WHERE tenant_id = :t AND code = :c',
                ['t' => $this->tenantId, 'c' => 'LEG-' . $r['c0d']]
            );
            if ($existing) continue;

            $params = [
                'tenant_id'        => $this->tenantId,
                'category'         => 'equipment',
                'incident_type_id' => $typeId,
                'asset_id'         => $assetId,
                'code'             => 'LEG-' . $r['c0d'],
                'title'            => $r['incident_description']
                    ? mb_substr((string) $r['incident_description'], 0, 200)
                    : 'Legacy incident #' . $r['c0d'],
                'description'      => $r['incident_description'] ?? null,
                'severity'         => strtolower((string) ($r['severity'] ?? 'medium')) ?: 'medium',
                'status'           => $this->mapLegacyStatus((string) ($r['incident_status'] ?? '')),
                'start_at'         => $r['incident_start_date'] ?? null,
                'end_at'           => $r['incident_end_date'] ?? null,
            ];
            if (!$this->dry) { $id = (int) DB::insert('incidents', $params); $this->map['incident'][(int)$r['c0d']] = $id; }
            $n++;
        }
        return $n;
    }

    private function mapLegacyStatus(string $s): string
    {
        $s = strtoupper($s);
        return match (true) {
            str_contains($s, 'CLOSED') && str_contains($s, 'PERMANENT') => 'closed_permanent',
            str_contains($s, 'CLOSED')     => 'closed_temporary',
            $s === 'OPEN' || $s === ''     => 'open',
            default                        => 'open',
        };
    }

    /** ─── Checklists → PM templates ────────────────────────────────────
     *
     * Legacy → orchview2:
     *   checklists                → pm_templates
     *   checklists_items          → pm_template_tasks (input_type=options if legacy feedback_type=OPTIONS)
     *   checklists_entries        → work_orders (wo_type=preventive, status=completed)
     *   checklists_items_entries  → work_order_tasks (response_value=response, result_note=remarks, remedy_note=remedies)
     *   checklists_attachments    → attachments (owner_type=work_order)
     *
     * Historical entries all default to `status=completed` per your call.
     */
    private function migrateChecklists(): int
    {
        $rows = $this->legacy->query('SELECT * FROM checklists')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $legacyId = (int) $r['c0d'];
            $code = 'legacy_chk_' . $legacyId;
            $existing = (int) DB::value(
                'SELECT template_id FROM pm_templates WHERE tenant_id = :t AND code = :c',
                ['t' => $this->tenantId, 'c' => $code]
            );
            if ($existing) { $this->map['checklist'][$legacyId] = $existing; continue; }

            $intervalDays = $this->frequencyToDays((string) ($r['checklist_frequency'] ?? ''));
            $assetTypeId  = $this->assetTypeIdForLegacyChecklistFor((string) ($r['checklist_for'] ?? ''));

            $params = [
                'tenant_id'     => $this->tenantId,
                'asset_type_id' => $assetTypeId,
                'code'          => $code,
                'name'          => $r['checklist_name'] ?: ('Legacy Checklist #' . $legacyId),
                'description'   => $r['checklist_description'] ?? null,
                'interval_days' => $intervalDays,
                'lead_days'     => 1,
                'priority'      => 'medium',
                'is_active'     => 1,
            ];
            if (!$this->dry) {
                $id = (int) DB::insert('pm_templates', $params);
                $this->map['checklist'][$legacyId] = $id;
            } else {
                // Dry-run: fake a positive ID so child steps count correctly.
                $this->map['checklist'][$legacyId] = -$legacyId;
            }
            $n++;
        }
        return $n;
    }

    private function migrateChecklistItems(): int
    {
        $rows = $this->legacy->query('SELECT * FROM checklists_items ORDER BY checklist_id, checklist_item_rank')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $legacyChecklistId = (int) $r['checklist_id'];
            $templateId = $this->map['checklist'][$legacyChecklistId] ?? null;
            if (!$templateId) continue;

            $legacyItemId = (int) $r['c0d'];
            $existing = (int) DB::value(
                'SELECT template_task_id FROM pm_template_tasks WHERE template_id = :tpl AND sort_order = :o AND title = :t',
                ['tpl' => $templateId, 'o' => (int) $r['checklist_item_rank'], 't' => (string) $r['checklist_item_description']]
            );
            if ($existing) { $this->map['checklist_item'][$legacyItemId] = $existing; continue; }

            $feedbackType = strtoupper((string) ($r['checklist_item_feedback_type'] ?? ''));
            $inputType    = $feedbackType === 'OPTIONS' ? 'options' : 'text';
            $optionsJson  = null;
            if ($inputType === 'options') {
                $opts = array_map('trim', explode(',', (string) $r['checklist_item_feedback']));
                $opts = array_values(array_filter($opts, fn($o) => $o !== ''));
                $optionsJson = $opts ? json_encode($opts) : null;
            }

            $params = [
                'template_id'  => $templateId,
                'sort_order'   => (int) $r['checklist_item_rank'],
                'title'        => (string) $r['checklist_item_description'],
                'description'  => null,
                'input_type'   => $inputType,
                'options_json' => $optionsJson,
                'is_required'  => 1,
            ];
            if (!$this->dry) {
                $id = (int) DB::insert('pm_template_tasks', $params);
                $this->map['checklist_item'][$legacyItemId] = $id;
            } else {
                $this->map['checklist_item'][$legacyItemId] = -$legacyItemId;
            }
            $n++;
        }
        return $n;
    }

    /** checklists_entries → work_orders (wo_type=preventive, status=completed). */
    private function migrateChecklistEntries(): int
    {
        $rows = $this->legacy->query('SELECT * FROM checklists_entries')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $legacyEntryId  = (int) $r['c0d'];
            $legacyChecklistId = (int) $r['checklist_id'];
            $templateId = $this->map['checklist'][$legacyChecklistId] ?? null;
            if (!$templateId) continue;

            // Resolve associated asset via checklist_generator_details.
            $detail = $this->legacy->prepare('SELECT generator_id, checklist_gen_hrs FROM checklist_generator_details WHERE checklist_entry_id = :e LIMIT 1');
            $detail->execute(['e' => $legacyEntryId]);
            $det = $detail->fetch();
            $assetId = null;
            $genHrs  = null;
            if ($det) {
                $legacyGenId = (int) $det['generator_id'];
                if ($legacyGenId) $assetId = $this->map['asset'][$legacyGenId] ?? null;
                $genHrs = $det['checklist_gen_hrs'];
            }

            $code = 'LEG-CHK-' . $legacyEntryId;
            $existing = (int) DB::value(
                'SELECT work_order_id FROM work_orders WHERE tenant_id = :t AND code = :c',
                ['t' => $this->tenantId, 'c' => $code]
            );
            if ($existing) { $this->map['checklist_entry'][$legacyEntryId] = $existing; continue; }

            $completedAt = trim(($r['entry_date'] ?? '') . ' ' . ($r['entry_time'] ?? '00:00:00'));
            $completedAt = $completedAt ?: null;

            $params = [
                'tenant_id'    => $this->tenantId,
                'code'         => $code,
                'title'        => 'PM (legacy): ' . ($r['general_remarks'] ? mb_substr((string) $r['general_remarks'], 0, 120) : $code),
                'description'  => trim(($r['general_remarks'] ?? '') . "\n" . ($r['supervisor_remarks'] ?? '')),
                'wo_type'      => 'preventive',
                'priority'     => 'medium',
                'status'       => 'completed',
                'asset_id'     => $assetId,
                'assigned_to'  => !empty($r['supervisor_id']) ? ($this->map['user'][(int) $r['supervisor_id']] ?? null) : null,
                'scheduled_at' => $completedAt,
                'due_at'       => $completedAt,
                'started_at'   => $completedAt,
                'completed_at' => $completedAt,
                'actual_hours' => $genHrs !== null && is_numeric($genHrs) ? (float) $genHrs : null,
                'notes'        => $genHrs !== null ? ('Generator hours at check: ' . $genHrs) : null,
            ];
            if (!$this->dry) {
                $id = (int) DB::insert('work_orders', $params);
                $this->map['checklist_entry'][$legacyEntryId] = $id;
            } else {
                $this->map['checklist_entry'][$legacyEntryId] = -$legacyEntryId;
            }
            $n++;
        }
        return $n;
    }

    /** checklists_items_entries → work_order_tasks. */
    private function migrateChecklistItemEntries(): int
    {
        $rows = $this->legacy->query('SELECT * FROM checklists_items_entries')->fetchAll();
        $n = 0;
        foreach ($rows as $r) {
            $woId = $this->map['checklist_entry'][(int) $r['checklist_entry_id']] ?? null;
            if (!$woId) continue;

            $legacyItemId = (int) $r['checklist_item_id'];
            $tplTaskId    = $this->map['checklist_item'][$legacyItemId] ?? null;

            // Fetch original item so we can carry the input_type + options.
            $inputType = 'text';
            $optionsJson = null;
            $title = 'Legacy item #' . $legacyItemId;
            if ($tplTaskId && $tplTaskId > 0) {
                $orig = DB::one('SELECT title, input_type, options_json FROM pm_template_tasks WHERE template_task_id = :id', ['id' => $tplTaskId]);
                if ($orig) {
                    $title       = $orig['title'];
                    $inputType   = $orig['input_type']   ?: 'text';
                    $optionsJson = $orig['options_json'] ?: null;
                }
            } elseif ($this->dry) {
                // In dry-run we haven't inserted the template task yet — fetch from legacy.
                $legacyItem = $this->legacy->prepare('SELECT * FROM checklists_items WHERE c0d = :id LIMIT 1');
                $legacyItem->execute(['id' => $legacyItemId]);
                $legacyRow = $legacyItem->fetch();
                if ($legacyRow) {
                    $title = (string) $legacyRow['checklist_item_description'];
                    if (strtoupper((string) $legacyRow['checklist_item_feedback_type']) === 'OPTIONS') {
                        $inputType   = 'options';
                        $optionsJson = json_encode(array_map('trim', explode(',', (string) $legacyRow['checklist_item_feedback'])));
                    }
                }
            }

            $response = trim((string) ($r['response'] ?? ''));
            $remarks  = trim((string) ($r['remarks']  ?? ''));
            $remedy   = trim((string) ($r['remedies'] ?? ''));

            $params = [
                'work_order_id'  => $woId,
                'sort_order'     => 0,
                'title'          => $title,
                'description'    => null,
                'input_type'     => $inputType,
                'options_json'   => $optionsJson,
                'is_required'    => 1,
                'is_done'        => 1,
                'response_value' => $response !== '' ? mb_substr($response, 0, 120) : null,
                'result_note'    => $remarks  !== '' ? $remarks : null,
                'remedy_note'    => $remedy   !== '' ? $remedy  : null,
                'done_at'        => null,
            ];
            if (!$this->dry) {
                DB::insert('work_order_tasks', $params);
            }
            $n++;
        }
        return $n;
    }

    /** checklists_attachments → attachments (owner_type=work_order) + copy the physical files. */
    private function migrateChecklistAttachments(): int
    {
        $rows = $this->legacy->query('SELECT * FROM checklists_attachments WHERE COALESCE(deleted, 0) = 0')->fetchAll();
        $legacyRoot = (string) env('LEGACY_ATTACHMENTS_ROOT', 'C:\wamp64\www\orchview.rookasystems.com\attachments');
        $legacyRoot = rtrim(str_replace('\\', '/', $legacyRoot), '/');
        $orchviewUploads = str_replace('\\', '/', STORAGE) . '/uploads';

        $n = 0; $copied = 0; $missing = 0;
        foreach ($rows as $r) {
            $woId = $this->map['checklist_entry'][(int) $r['checklist_entry_id']] ?? null;
            if (!$woId) continue;

            $fileName    = (string) $r['file_name'];
            $legacySrc   = $legacyRoot . '/checklists/' . $fileName;
            $orchviewRel = 'legacy/checklists/' . $fileName;
            $orchviewDst = $orchviewUploads . '/' . $orchviewRel;

            $sizeBytes = null;
            if (!$this->dry) {
                if (is_file($legacySrc)) {
                    $dstDir = dirname($orchviewDst);
                    if (!is_dir($dstDir)) { @mkdir($dstDir, 0775, true); }
                    if (@copy($legacySrc, $orchviewDst)) {
                        $sizeBytes = filesize($orchviewDst) ?: null;
                        $copied++;
                    } else {
                        error_log("[legacy:migrate] copy failed: {$legacySrc}");
                        $missing++;
                    }
                } else {
                    $missing++;
                }
            }

            $params = [
                'tenant_id'     => $this->tenantId,
                'owner_type'    => 'work_order',
                'owner_id'      => $woId,
                'file_name'     => $orchviewRel,
                'original_name' => $fileName,
                'file_ext'      => $r['file_ext'] ?? null,
                'mime_type'     => null,
                'size_bytes'    => $sizeBytes,
                'caption'       => $r['attachment_caption'] ?? null,
                'uploaded_by'   => !empty($r['created_by']) ? ($this->map['user'][(int) $r['created_by']] ?? null) : null,
            ];
            if (!$this->dry) {
                DB::insert('attachments', $params);
            }
            $n++;
        }
        if (!$this->dry && ($copied || $missing)) {
            fwrite(STDOUT, "    (files: {$copied} copied, {$missing} missing on disk)\n");
        }
        return $n;
    }

    /** Map legacy frequency label to interval_days. */
    private function frequencyToDays(string $f): int
    {
        $f = strtoupper(trim($f));
        return match (true) {
            str_contains($f, 'DAILY')     => 1,
            str_contains($f, 'WEEKLY')    => 7,
            str_contains($f, 'FORTNIGHT') => 14,
            str_contains($f, 'MONTHLY')   => 30,
            str_contains($f, 'QUARTER')   => 90,
            str_contains($f, 'ANNUAL')    => 365,
            str_contains($f, 'YEAR')      => 365,
            default                       => 30,
        };
    }

    /** Guess orchview2 asset_type_id from legacy checklists.checklist_for text. */
    private function assetTypeIdForLegacyChecklistFor(string $for): ?int
    {
        $for = strtolower($for);
        $code = match (true) {
            str_contains($for, 'generator')  => 'generator',
            str_contains($for, 'aircon')     => 'aircon',
            str_contains($for, 'transformer')=> 'transformer',
            str_contains($for, 'stabilizer') => 'stabilizer',
            str_contains($for, 'panel')      => 'panel',
            str_contains($for, 'motor')      => 'motor_starter',
            str_contains($for, 'light')      => 'lighting',
            default                          => null,
        };
        if (!$code) return null;
        $row = DB::one(
            'SELECT asset_type_id FROM asset_types WHERE tenant_id = :t AND code = :c',
            ['t' => $this->tenantId, 'c' => $code]
        );
        return $row ? (int) $row['asset_type_id'] : null;
    }

    /** ─── Plant readings (power_plant_params) ─────────────────────────
     *
     * Legacy → orchview2:
     *   power_plant_params → plant_reading_sessions (1 row) + plant_readings (up to 22 rows per session)
     *   power_params_attachments → attachments (owner_type='reading_session')
     */
    private function migratePlantReadings(): int
    {
        // Field → (group, unit) mapping. Everything else on the legacy row is metadata.
        $paramMap = [
            'v_l_1'    => ['voltage',      'V'],
            'v_l_2'    => ['voltage',      'V'],
            'v_l_3'    => ['voltage',      'V'],
            'v_l_12'   => ['voltage',      'V'],
            'v_l_23'   => ['voltage',      'V'],
            'v_l_31'   => ['voltage',      'V'],
            'kw_l_1'   => ['power_kw',     'kW'],
            'kw_l_2'   => ['power_kw',     'kW'],
            'kw_l_3'   => ['power_kw',     'kW'],
            'kvar_l_1' => ['power_kvar',   'kVAr'],
            'kvar_l_2' => ['power_kvar',   'kVAr'],
            'kvar_l_3' => ['power_kvar',   'kVAr'],
            'kva_l_1'  => ['power_kva',    'kVA'],
            'kva_l_2'  => ['power_kva',    'kVA'],
            'kva_l_3'  => ['power_kva',    'kVA'],
            'a_1'      => ['current',      'A'],
            'a_2'      => ['current',      'A'],
            'a_3'      => ['current',      'A'],
        ];

        $rows = $this->legacy->query('SELECT * FROM power_plant_params')->fetchAll();
        $sessions = 0; $readings = 0;
        foreach ($rows as $r) {
            $legacyId = (int) $r['c0d'];
            $takenAt  = trim(($r['reading_date'] ?? '') . ' ' . ($r['reading_time'] ?? '00:00:00'));

            $existing = (int) DB::value(
                'SELECT session_id FROM plant_reading_sessions WHERE tenant_id = :t AND legacy_id = :l',
                ['t' => $this->tenantId, 'l' => $legacyId]
            );
            if ($existing) {
                $this->map['reading_session'][$legacyId] = $existing;
                continue;
            }

            $status = strtolower((string) ($r['power_plant_param_status'] ?? 'closed'));
            if (!in_array($status, ['draft', 'closed', 'reviewed'], true)) { $status = 'closed'; }

            $sessionParams = [
                'tenant_id'          => $this->tenantId,
                'asset_id'           => null,
                'site_id'            => null,
                'taken_at'           => $takenAt ?: null,
                'taken_by'           => !empty($r['created_by']) ? ($this->map['user'][(int) $r['created_by']] ?? null) : null,
                'status'             => $status,
                'remarks'            => trim((string) ($r['power_param_remarks'] ?? '')) ?: null,
                'supervisor_remarks' => trim((string) ($r['client_supervisor_remarks'] ?? '')) ?: null,
                'legacy_id'          => $legacyId,
            ];

            if (!$this->dry) {
                $sessionId = (int) DB::insert('plant_reading_sessions', $sessionParams);
                $this->map['reading_session'][$legacyId] = $sessionId;
            } else {
                $sessionId = -$legacyId;
                $this->map['reading_session'][$legacyId] = $sessionId;
            }
            $sessions++;

            foreach ($paramMap as $col => [$group, $unit]) {
                if (!array_key_exists($col, $r) || $r[$col] === null || $r[$col] === '') continue;
                $val = (float) $r[$col];
                if (!$this->dry) {
                    DB::insert('plant_readings', [
                        'tenant_id'       => $this->tenantId,
                        'session_id'      => $sessionId,
                        'asset_id'        => null,
                        'parameter_key'   => $col,
                        'parameter_group' => $group,
                        'value_number'    => $val,
                        'unit'            => $unit,
                        'taken_at'        => $takenAt ?: null,
                    ]);
                }
                $readings++;
            }
        }
        fwrite(STDOUT, "    ({$readings} readings across {$sessions} sessions)\n");
        return $sessions;
    }

    /** power_params_attachments → attachments (owner_type='reading_session') + copy files. */
    private function migratePlantReadingAttachments(): int
    {
        $rows = $this->legacy->query('SELECT * FROM power_params_attachments WHERE COALESCE(deleted, 0) = 0')->fetchAll();
        $legacyRoot = (string) env('LEGACY_ATTACHMENTS_ROOT', 'C:\wamp64\www\orchview.rookasystems.com\attachments');
        $legacyRoot = rtrim(str_replace('\\', '/', $legacyRoot), '/');
        $orchviewUploads = str_replace('\\', '/', STORAGE) . '/uploads';

        $n = 0; $copied = 0; $missing = 0;
        foreach ($rows as $r) {
            $sessionId = $this->map['reading_session'][(int) $r['power_plant_param_id']] ?? null;
            if (!$sessionId || $sessionId < 0) continue;

            $fileName    = (string) $r['file_name'];
            $legacySrc   = $legacyRoot . '/power_params_photos/' . $fileName;
            $orchviewRel = 'legacy/plant_readings/' . $fileName;
            $orchviewDst = $orchviewUploads . '/' . $orchviewRel;

            $sizeBytes = null;
            if (!$this->dry) {
                if (is_file($legacySrc)) {
                    $dstDir = dirname($orchviewDst);
                    if (!is_dir($dstDir)) { @mkdir($dstDir, 0775, true); }
                    if (@copy($legacySrc, $orchviewDst)) {
                        $sizeBytes = filesize($orchviewDst) ?: null;
                        $copied++;
                    } else {
                        $missing++;
                    }
                } else {
                    $missing++;
                }
            }

            $params = [
                'tenant_id'     => $this->tenantId,
                'owner_type'    => 'reading_session',
                'owner_id'      => $sessionId,
                'file_name'     => $orchviewRel,
                'original_name' => $fileName,
                'file_ext'      => $r['file_ext'] ?? null,
                'mime_type'     => null,
                'size_bytes'    => $sizeBytes,
                'caption'       => $r['attachment_caption'] ?? null,
                'uploaded_by'   => !empty($r['created_by']) ? ($this->map['user'][(int) $r['created_by']] ?? null) : null,
            ];
            if (!$this->dry) {
                DB::insert('attachments', $params);
            }
            $n++;
        }
        if (!$this->dry && ($copied || $missing)) {
            fwrite(STDOUT, "    (files: {$copied} copied, {$missing} missing on disk)\n");
        }
        return $n;
    }
}
