<?php

declare(strict_types=1);

$apply = in_array('--apply', $argv, true);
$sourceConfigPath = '/workspace/wwwroot/tnxl/project/application/config/prod/tnxl/database.php';
$targetConfigPath = '/workspace/wwwroot/tnxlgstyj/project/application/config/prod/tnxlgstyj/database.php';

function dbFromConfig(string $path): mysqli
{
    $config = require $path;
    $db = new mysqli($config['hostname'], $config['username'], $config['password'], $config['database']);
    if ($db->connect_errno) {
        throw new RuntimeException('Database connection failed for ' . $config['database']);
    }
    $db->set_charset('utf8mb4');
    return $db;
}

function fetchAll(mysqli $db, string $sql): array
{
    $result = $db->query($sql);
    if ($result === false) {
        throw new RuntimeException($db->error);
    }
    return $result->fetch_all(MYSQLI_ASSOC);
}

function rowsByUuid(array $rows): array
{
    $out = [];
    foreach ($rows as $row) {
        $out[$row['uuid']] = $row;
    }
    return $out;
}

function categoryPath(string $uuid, array $byUuid, array &$memo): string
{
    if (isset($memo[$uuid])) {
        return $memo[$uuid];
    }
    if (!isset($byUuid[$uuid])) {
        throw new RuntimeException('Missing category UUID: ' . $uuid);
    }
    $row = $byUuid[$uuid];
    $name = trim((string)$row['name']);
    if ((string)$row['puuid'] === '0') {
        return $memo[$uuid] = $name;
    }
    return $memo[$uuid] = categoryPath((string)$row['puuid'], $byUuid, $memo) . '/' . $name;
}

function mappedSourcePath(array $row, string $path): string
{
    $renames = ['田径项目', '水上项目', '冬季雪上'];
    $name = trim((string)$row['name']);
    if ((int)$row['type'] === 1 && in_array($name, $renames, true)) {
        $parent = substr($path, 0, (int)strrpos($path, '/'));
        if ($parent === '专项营养/耐力类项目') {
            return $parent . '/' . $name . '（耐力类项目）';
        }
    }
    return $path;
}

function uuidV4(): string
{
    $data = random_bytes(16);
    $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
    $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
    return strtoupper(vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4)));
}

function bindAllStrings(mysqli_stmt $stmt, array &$values): void
{
    $types = str_repeat('s', count($values));
    $refs = [$types];
    foreach ($values as $index => $_) {
        $refs[] = &$values[$index];
    }
    if (!call_user_func_array([$stmt, 'bind_param'], $refs)) {
        throw new RuntimeException('bind_param failed');
    }
}

$source = dbFromConfig($sourceConfigPath);
$target = dbFromConfig($targetConfigPath);

$sourceCategories = fetchAll($source, "SELECT * FROM tn_article_cate WHERE type IN (1,2) AND del_flag=0 ORDER BY level,id");
$targetCategories = fetchAll($target, "SELECT * FROM tn_article_cate WHERE type IN (1,2) ORDER BY level,id");
$sourceByUuid = rowsByUuid($sourceCategories);
$targetByUuid = rowsByUuid($targetCategories);
$sourceMemo = [];
$targetMemo = [];
$targetByKey = [];

foreach ($targetCategories as $category) {
    $path = categoryPath((string)$category['uuid'], $targetByUuid, $targetMemo);
    $key = ((int)$category['type']) . '|' . $path;
    if (isset($targetByKey[$key])) {
        throw new RuntimeException('Duplicate target category path: ' . $key);
    }
    $targetByKey[$key] = $category;
}

$categoryMap = [];
$missingCategories = [];
foreach ($sourceCategories as $category) {
    $path = categoryPath((string)$category['uuid'], $sourceByUuid, $sourceMemo);
    $mappedPath = mappedSourcePath($category, $path);
    $key = ((int)$category['type']) . '|' . $mappedPath;
    if (!isset($targetByKey[$key])) {
        $missingCategories[] = $key;
        continue;
    }
    $categoryMap[$category['uuid']] = $targetByKey[$key]['uuid'];
}

$sourceArticles = fetchAll($source, 'SELECT * FROM tn_article ORDER BY id');
$sourceNutrition = fetchAll($source, 'SELECT * FROM tn_nutrition ORDER BY id');
$targetArticles = fetchAll($target, 'SELECT * FROM tn_article ORDER BY id');
$targetNutrition = fetchAll($target, 'SELECT * FROM tn_nutrition ORDER BY id');
$targetArticleTitles = array_fill_keys(array_column($targetArticles, 'title'), true);
$targetNutritionNames = array_fill_keys(array_column($targetNutrition, 'name'), true);

$unmappedArticles = array_values(array_filter($sourceArticles, function ($row) use ($categoryMap) {
    return !isset($categoryMap[$row['article_cate_uuid']]);
}));
$unmappedNutrition = array_values(array_filter($sourceNutrition, function ($row) use ($categoryMap) {
    return !isset($categoryMap[$row['article_cate_uuid']]);
}));
$articleConflicts = array_values(array_filter($sourceArticles, function ($row) use ($targetArticleTitles) {
    return isset($targetArticleTitles[$row['title']]);
}));
$nutritionConflicts = array_values(array_filter($sourceNutrition, function ($row) use ($targetNutritionNames) {
    return isset($targetNutritionNames[$row['name']]);
}));

$report = [
    'mode' => $apply ? 'apply' : 'dry-run',
    'source_categories' => count($sourceCategories),
    'target_categories' => count($targetCategories),
    'mapped_categories' => count($categoryMap),
    'missing_categories' => $missingCategories,
    'source_articles' => count($sourceArticles),
    'target_articles_before' => count($targetArticles),
    'source_nutrition' => count($sourceNutrition),
    'target_nutrition_before' => count($targetNutrition),
    'unmapped_articles' => count($unmappedArticles),
    'unmapped_nutrition' => count($unmappedNutrition),
    'article_title_conflicts' => array_column($articleConflicts, 'title'),
    'nutrition_name_conflicts' => array_column($nutritionConflicts, 'name'),
    'created_articles' => 0,
    'created_nutrition' => 0,
    'updated_category_metadata' => 0,
];

if ($missingCategories || $unmappedArticles || $unmappedNutrition || $articleConflicts || $nutritionConflicts) {
    echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;
    exit(2);
}

if (!$apply) {
    echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;
    exit(0);
}

$target->begin_transaction();
try {
    $articleSql = 'INSERT INTO tn_article '
        . '(uuid,article_cate_uuid,tag,keyword,title,img,summary,content,status,create_time,create_by) '
        . 'VALUES (?,?,?,?,?,?,?,?,?,?,?)';
    $articleStmt = $target->prepare($articleSql);
    if ($articleStmt === false) {
        throw new RuntimeException($target->error);
    }
    foreach ($sourceArticles as $row) {
        $values = [
            uuidV4(), $categoryMap[$row['article_cate_uuid']], $row['tag'], $row['keyword'], $row['title'],
            $row['img'], $row['summary'], $row['content'], (string)$row['status'], $row['create_time'], $row['create_by'],
        ];
        bindAllStrings($articleStmt, $values);
        if (!$articleStmt->execute()) {
            throw new RuntimeException('Article insert failed: ' . $articleStmt->error);
        }
        $report['created_articles']++;
    }

    $nutritionSql = 'INSERT INTO tn_nutrition '
        . '(uuid,article_cate_uuid,name,en_name,label,specifications,manufacturer,flavor,use_method,scenes,ingredients,synopsis,img_path,status,is_del,create_time,create_by) '
        . 'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
    $nutritionStmt = $target->prepare($nutritionSql);
    if ($nutritionStmt === false) {
        throw new RuntimeException($target->error);
    }
    foreach ($sourceNutrition as $row) {
        $values = [
            uuidV4(), $categoryMap[$row['article_cate_uuid']], $row['name'], $row['en_name'], $row['label'],
            $row['specifications'], $row['manufacturer'], $row['flavor'], $row['use_method'], $row['scenes'],
            $row['ingredients'], $row['synopsis'], $row['img_path'], (string)$row['status'], (string)$row['is_del'],
            $row['create_time'], $row['create_by'],
        ];
        bindAllStrings($nutritionStmt, $values);
        if (!$nutritionStmt->execute()) {
            throw new RuntimeException('Nutrition insert failed: ' . $nutritionStmt->error);
        }
        $report['created_nutrition']++;
    }

    $categoryStmt = $target->prepare('UPDATE tn_article_cate SET status=?, del_flag=?, article_quantity=? WHERE uuid=?');
    if ($categoryStmt === false) {
        throw new RuntimeException($target->error);
    }
    foreach ($sourceCategories as $category) {
        $values = [
            (string)$category['status'], (string)$category['del_flag'],
            (string)$category['article_quantity'], $categoryMap[$category['uuid']],
        ];
        bindAllStrings($categoryStmt, $values);
        if (!$categoryStmt->execute()) {
            throw new RuntimeException('Category metadata update failed: ' . $categoryStmt->error);
        }
        $report['updated_category_metadata']++;
    }

    $target->commit();
} catch (Throwable $error) {
    $target->rollback();
    $report['error'] = $error->getMessage();
    echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;
    exit(1);
}

$report['target_articles_after'] = (int)fetchAll($target, 'SELECT COUNT(*) AS c FROM tn_article')[0]['c'];
$report['target_nutrition_after'] = (int)fetchAll($target, 'SELECT COUNT(*) AS c FROM tn_nutrition')[0]['c'];
echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . PHP_EOL;