<?php
/* ============================================================
 * Larspeenghar — one-time migration: JSON files  →  MySQL
 *
 * Reads the old data/*.json files (or an uploaded full backup) and
 * loads them into the database created by schema.sql.
 *
 * USAGE (run once, then DELETE this file):
 *   1. Create the DB + tables:   mysql -u USER -p DBNAME < schema.sql
 *   2. Fill in config.php 'db' credentials.
 *   3. Visit  https://yourdomain.com/migrate_import.php?key=YOUR_SECRET
 *      (set the same secret in config.php as 'migrate_key').
 *
 * It remaps the old string ids to new numeric ids in two passes so that
 * linked sire/dam relationships survive.
 * ============================================================ */

header('Content-Type: text/plain; charset=utf-8');
require __DIR__ . '/db.php';

$cfg = file_exists(__DIR__ . '/config.php') ? include __DIR__ . '/config.php' : [];
$secret = $cfg['migrate_key'] ?? '';
if ($secret === '' || ($_GET['key'] ?? '') !== $secret) {
    http_response_code(403);
    exit("Forbidden. Set 'migrate_key' in config.php and pass ?key=...\n");
}

$pdo = lp_db();
$DATA = $cfg['data_dir'] ?? __DIR__ . '/data';

function jload($path) {
    if (!file_exists($path)) return [];
    $d = json_decode(file_get_contents($path), true);
    return is_array($d) ? $d : [];
}
function say($s) { echo $s . "\n"; flush(); }

$pdo->beginTransaction();
try {
    /* ---- 1. OWNERS (herds + individual owners → unified table) ---- */
    $ownerMap = []; // oldStringId => newBigintId
    $insOwner = $pdo->prepare(
        "INSERT INTO owners (type, code, herd_name, name, breed_focus, phone, email, province, district, address, notes)
         VALUES (:type,:code,:herd_name,:name,:breed_focus,:phone,:email,:province,:district,:address,:notes)"
    );
    foreach ([['lp_owners.json','herd'], ['lp_indiv_owners.json','individual']] as [$file, $type]) {
        foreach (jload("$DATA/$file") as $o) {
            $insOwner->execute([
                ':type' => $type,
                ':code' => $o['herdCode'] ?? $o['code'] ?? null,
                ':herd_name' => $o['herdName'] ?? null,
                ':name' => $o['name'] ?? null,
                ':breed_focus' => $o['breedFocus'] ?? null,
                ':phone' => $o['phone'] ?? null,
                ':email' => $o['email'] ?? null,
                ':province' => $o['province'] ?? null,
                ':district' => $o['district'] ?? null,
                ':address' => $o['address'] ?? null,
                ':notes' => $o['notes'] ?? null,
            ]);
            if (!empty($o['id'])) $ownerMap[$o['id']] = (int)$pdo->lastInsertId();
        }
    }
    say('Owners imported: ' . count($ownerMap));

    /* ---- 2. USERS ---- */
    $users = jload("$DATA/users.json");
    if (!$users) $users = jload("$DATA/lp_users_v1.json");
    $insUser = $pdo->prepare(
        "INSERT INTO users (id, username, password_hash, role, owner_id, name)
         VALUES (?,?,?,?,?,?)
         ON DUPLICATE KEY UPDATE username=VALUES(username)"
    );
    $uCount = 0;
    foreach ($users as $u) {
        $pw = $u['password'] ?? '';
        // Already-hashed passwords start with $2y$/$2b$/$argon; else hash now.
        $hash = preg_match('/^\$(2[aby]|argon)/', $pw) ? $pw : password_hash($pw ?: bin2hex(random_bytes(6)), PASSWORD_DEFAULT);
        $oid = isset($u['ownerId']) && isset($ownerMap[$u['ownerId']]) ? $ownerMap[$u['ownerId']] : null;
        $insUser->execute([
            $u['id'] ?? ('u' . $uCount), $u['username'] ?? ('user' . $uCount), $hash,
            ($u['role'] ?? 'owner') === 'admin' ? 'admin' : 'owner', $oid, $u['name'] ?? null,
        ]);
        $uCount++;
    }
    say("Users imported: $uCount");

    /* ---- 3. ANIMALS — pass 1: insert, build id map (no parent links yet) ---- */
    $animals = jload("$DATA/animals.json");
    if (!$animals) $animals = jload("$DATA/larspeenghar_v7.json");
    $animalMap = []; // oldStringId => newBigintId

    // Minimal inline column mapper (mirrors api_db.php animal_to_columns, minus parent links)
    $toCols = function ($a) use ($ownerMap) {
        $owner = isset($a['ownerId']) && isset($ownerMap[$a['ownerId']]) ? $ownerMap[$a['ownerId']] : null;
        return [
            'reg_id' => $a['regId'] ?? null, 'ear_tag' => $a['earTag'] ?? null, 'name' => $a['name'] ?? null,
            'breed' => $a['breed'] ?? null, 'sex' => in_array($a['sex'] ?? '', ['Male','Female'], true) ? $a['sex'] : null,
            'role' => $a['role'] ?? null, 'dob' => ($a['dob'] ?? '') !== '' ? $a['dob'] : null,
            'color' => $a['color'] ?? null, 'lactation' => ($a['lact'] ?? '') !== '' ? (int)$a['lact'] : null,
            'owner_id' => $owner, 'province' => $a['province'] ?? null, 'district' => $a['district'] ?? null,
            'sire_name' => $a['sireName'] ?? null, 'sire_reg' => $a['sireId'] ?? null, 'sire_breed' => $a['sireBreed'] ?? null,
            'dam_name' => $a['damName'] ?? null, 'dam_reg' => $a['damId'] ?? null, 'dam_breed' => $a['damBreed'] ?? null,
            'ss_name' => $a['ssName'] ?? null, 'ss_reg' => $a['ssId'] ?? null,
            'sd_name' => $a['sdName'] ?? null, 'sd_reg' => $a['sdId'] ?? null,
            'ds_name' => $a['dsName'] ?? null, 'ds_reg' => $a['dsId'] ?? null,
            'dd_name' => $a['ddName'] ?? null, 'dd_reg' => $a['ddId'] ?? null,
            'milk_305' => ($a['milk'] ?? '') !== '' ? $a['milk'] : null,
            'fat_pct' => ($a['fat'] ?? '') !== '' ? $a['fat'] : null,
            'prot_pct' => ($a['prot'] ?? '') !== '' ? $a['prot'] : null,
            'peak' => ($a['peak'] ?? '') !== '' ? $a['peak'] : null,
            'dim' => ($a['dim'] ?? '') !== '' ? (int)$a['dim'] : null,
            'scs' => ($a['scs'] ?? '') !== '' ? $a['scs'] : null,
            'pending_status' => $a['pendingStatus'] ?? null, 'notes' => $a['notes'] ?? null,
            'breed_comp' => isset($a['breedComp']) && $a['breedComp'] ? json_encode($a['breedComp']) : null,
        ];
    };

    @mkdir(__DIR__ . '/uploads/animals', 0775, true);
    $cols = array_keys($toCols([]));
    $cols[] = 'extra'; $cols[] = 'photo';
    $insAnimal = $pdo->prepare(
        "INSERT INTO animals (" . implode(',', $cols) . ") VALUES (" . implode(',', array_map(fn($c) => ":$c", $cols)) . ")"
    );

    $aCount = 0;
    foreach ($animals as $a) {
        $c = $toCols($a);
        // overflow → extra
        $core = ['id','regId','earTag','name','breed','sex','role','dob','color','lact','ownerId','province','district',
            'sireLinkedId','damLinkedId','sireName','sireId','sireBreed','damName','damId','damBreed','ssName','ssId',
            'sdName','sdId','dsName','dsId','ddName','ddId','milk','fat','prot','peak','dim','scs','photo','breedComp',
            'pendingStatus','notes'];
        $extra = [];
        foreach ($a as $k => $v) if (!in_array($k, $core, true)) $extra[$k] = $v;
        $c['extra'] = $extra ? json_encode($extra) : null;

        // Photo: move base64 data-URL to a file, keep small/path photos as-is.
        $photo = $a['photo'] ?? '';
        $c['photo'] = null;
        $insAnimal->execute($c);
        $newId = (int)$pdo->lastInsertId();
        if (!empty($a['id'])) $animalMap[$a['id']] = $newId;
        if (is_string($photo) && strpos($photo, 'data:image') === 0) {
            $bin = base64_decode(substr($photo, strpos($photo, ',') + 1));
            if ($bin !== false) {
                $fn = "uploads/animals/$newId.jpg";
                file_put_contents(__DIR__ . "/$fn", $bin);
                $pdo->prepare("UPDATE animals SET photo=? WHERE id=?")->execute([$fn, $newId]);
            }
        } elseif ($photo !== '') {
            $pdo->prepare("UPDATE animals SET photo=? WHERE id=?")->execute([$photo, $newId]);
        }
        $aCount++;
    }
    say("Animals imported (pass 1): $aCount");

    /* ---- pass 2: wire up linked sire/dam using the id map ---- */
    $upd = $pdo->prepare("UPDATE animals SET sire_id=?, dam_id=? WHERE id=?");
    $linked = 0;
    foreach ($animals as $a) {
        if (empty($a['id']) || !isset($animalMap[$a['id']])) continue;
        $sid = isset($a['sireLinkedId']) && isset($animalMap[$a['sireLinkedId']]) ? $animalMap[$a['sireLinkedId']] : null;
        $did = isset($a['damLinkedId']) && isset($animalMap[$a['damLinkedId']]) ? $animalMap[$a['damLinkedId']] : null;
        if ($sid !== null || $did !== null) { $upd->execute([$sid, $did, $animalMap[$a['id']]]); $linked++; }
    }
    say("Linked parents wired: $linked");

    /* ---- 4. MESSAGES + SETTINGS ---- */
    foreach (jload("$DATA/messages.json") as $m) {
        $pdo->prepare(
            "INSERT INTO messages (from_user,to_user,from_name,type,subject,body,is_read,status,request_data,created_at)
             VALUES (?,?,?,?,?,?,?,?,?,NOW())"
        )->execute([
            $m['from'] ?? null, $m['to'] ?? null, $m['fromName'] ?? null, $m['type'] ?? null,
            $m['subject'] ?? null, $m['body'] ?? null, !empty($m['read']) ? 1 : 0, $m['status'] ?? null,
            isset($m['requestData']) ? json_encode($m['requestData']) : null,
        ]);
    }
    foreach (['settings' => 'cert', 'lp_cert_settings' => 'cert', 'lp_landing_i18n' => 'i18n', 'lp_breed_avg' => 'breed_avg'] as $file => $key) {
        $v = jload("$DATA/$file.json");
        if ($v) $pdo->prepare("INSERT INTO settings (k,v) VALUES (?,?) ON DUPLICATE KEY UPDATE v=VALUES(v)")
            ->execute([$key, json_encode($v)]);
    }

    $pdo->commit();
    say("\n✅ Migration complete. DELETE this file (migrate_import.php) now for security.");
} catch (Throwable $e) {
    $pdo->rollBack();
    http_response_code(500);
    say("\n❌ Migration failed and was rolled back: " . $e->getMessage());
}
