<?php
/**
 * Converts one_gram_gold_categories_full_seo.xlsx into database/categories.sql
 *
 * Expected columns (header names are flexible, case-insensitive):
 * - name: name / category / category_name / child_category
 * - parent: parent / parent_name / parent_category_name / parent_category
 * - slug: slug (optional; generated from name if missing)
 * - description: description (optional)
 * - meta_title: meta_title / meta title (optional)
 * - meta_description: meta_description / meta description (optional)
 * - image_path: image_path / image (optional)
 * - is_active: is_active / active / status (optional; defaults to 1)
 * - sort_order: sort_order / sort / order (optional; defaults to 0)
 *
 * Run from project root:
 *   php database/scripts/xlsx_to_categories_sql.php
 * Or:
 *   php database/scripts/xlsx_to_categories_sql.php path/to/file.xlsx
 */
declare(strict_types=1);

$projectRoot = dirname(__DIR__, 2);

// Allow passing a custom XLSX path as first arg.
// Defaults to the newer "full" file if present, otherwise falls back.
$argPath = $argv[1] ?? '';
$argPath = is_string($argPath) ? trim($argPath) : '';

if ($argPath !== '') {
    $xlsxPath = $argPath;
    if (!preg_match('/^[A-Za-z]:[\/\\\\]/', $xlsxPath)) {
        $xlsxPath = $projectRoot . DIRECTORY_SEPARATOR . $xlsxPath;
    }
} else {
    $preferred = $projectRoot . DIRECTORY_SEPARATOR . 'one_gram_gold_categories_full_seo.xlsx';
    $fallback  = $projectRoot . DIRECTORY_SEPARATOR . 'one_gram_gold_categories_seo.xlsx';
    $xlsxPath = is_readable($preferred) ? $preferred : $fallback;
}

$sqlPath  = $projectRoot . DIRECTORY_SEPARATOR . 'database' . DIRECTORY_SEPARATOR . 'categories.sql';

if (!is_readable($xlsxPath)) {
    fwrite(STDERR, "Error: XLSX not found at: {$xlsxPath}\n");
    exit(1);
}

try {
    $sheet = readXlsxFirstSheet($xlsxPath);
} catch (Throwable $e) {
    fwrite(STDERR, "Error: Failed to read XLSX. " . $e->getMessage() . "\n");
    exit(1);
}

if (count($sheet) < 2) {
    fwrite(STDERR, "Error: XLSX appears empty (needs header + at least 1 row).\n");
    exit(1);
}

$headerRaw = $sheet[0];
$headerMap = buildHeaderIndexMap($headerRaw);

$idxName = findHeaderIndex($headerMap, ['name', 'category', 'category_name', 'categoryname', 'child_category', 'childcategory']);
if ($idxName === null) {
    fwrite(STDERR, "Error: XLSX must have a name column (e.g. 'name' / 'category').\n");
    fwrite(STDERR, "Headers detected: " . implode(', ', array_keys($headerMap)) . "\n");
    exit(1);
}

$idxParent = findHeaderIndex($headerMap, ['parent', 'parent_name', 'parent_category_name', 'parentcategory', 'parentcategoryname', 'parent_category', 'parentcategory']);
$idxSlug = findHeaderIndex($headerMap, ['slug', 'url_slug', 'urlslug']);
$idxDescription = findHeaderIndex($headerMap, ['description', 'desc']);
$idxMetaTitle = findHeaderIndex($headerMap, ['meta_title', 'metatitle', 'meta title', 'seo_title', 'seotitle']);
$idxMetaDescription = findHeaderIndex($headerMap, ['meta_description', 'metadescription', 'meta description', 'seo_description', 'seodescription', 'seo_keywords', 'seokeywords', 'keywords']);
$idxImagePath = findHeaderIndex($headerMap, ['image_path', 'imagepath', 'image', 'image_url', 'imageurl']);
$idxIsActive = findHeaderIndex($headerMap, ['is_active', 'active', 'status', 'enabled']);
$idxSortOrder = findHeaderIndex($headerMap, ['sort_order', 'sort', 'order', 'sortorder', 'position']);

$rows = [];
$parentMeta = [];
for ($r = 1; $r < count($sheet); $r++) {
    $row = $sheet[$r];
    if (isRowBlank($row)) {
        continue;
    }

    $parentName = $idxParent === null ? '' : trim((string)($row[$idxParent] ?? ''));
    $name = trim((string)($row[$idxName] ?? ''));
    $slug = $idxSlug === null ? '' : trim((string)($row[$idxSlug] ?? ''));

    $description = $idxDescription === null ? null : normalizeNullableText($row[$idxDescription] ?? null);
    $metaTitle = $idxMetaTitle === null ? null : normalizeNullableText($row[$idxMetaTitle] ?? null);
    $metaDescription = $idxMetaDescription === null ? null : normalizeNullableText($row[$idxMetaDescription] ?? null);
    if ($metaTitle !== null) {
        $metaTitle = truncateUtf8($metaTitle, 255);
    }
    if ($metaDescription !== null) {
        $metaDescription = truncateUtf8($metaDescription, 500);
    }
    $imagePath = $idxImagePath === null ? null : normalizeNullableText($row[$idxImagePath] ?? null);

    $isActive = $idxIsActive === null ? 1 : (parseBoolLike($row[$idxIsActive] ?? null) ? 1 : 0);
    $sortOrder = $idxSortOrder === null ? 0 : parseIntLike($row[$idxSortOrder] ?? null, 0);

    // Rows with blank child but present parent are treated as root-category metadata rows.
    if ($name === '' && $parentName !== '') {
        if (!isset($parentMeta[$parentName])) {
            $parentMeta[$parentName] = [
                'description' => null,
                'meta_title' => null,
                'meta_description' => null,
                'image_path' => null,
                'is_active' => 1,
                'sort_order' => 0,
            ];
        }
        if ($description !== null) {
            $parentMeta[$parentName]['description'] = $description;
        }
        if ($metaTitle !== null) {
            $parentMeta[$parentName]['meta_title'] = $metaTitle;
        }
        if ($metaDescription !== null) {
            $parentMeta[$parentName]['meta_description'] = $metaDescription;
        }
        if ($imagePath !== null) {
            $parentMeta[$parentName]['image_path'] = $imagePath;
        }
        $parentMeta[$parentName]['is_active'] = $isActive;
        $parentMeta[$parentName]['sort_order'] = $sortOrder;
        continue;
    }

    if ($name === '') {
        continue;
    }

    $rows[] = [
        'name' => $name,
        'parent_name' => $parentName,
        'slug' => $slug,
        'description' => $description,
        'meta_title' => $metaTitle,
        'meta_description' => $metaDescription,
        'image_path' => $imagePath,
        'is_active' => $isActive,
        'sort_order' => $sortOrder,
    ];
}

if (count($rows) === 0) {
    fwrite(STDERR, "Error: No data rows found after parsing.\n");
    exit(1);
}

// Build categories as:
// - all distinct parent_category values become top-level categories
// - each row becomes a child category unique by (parent_category, child_category)
$now = date('Y-m-d H:i:s');
$usedSlugs = [];

$parentNameToId = [];
$childrenKeyToId = [];
$nodes = []; // id => node data

// 1) Create parents
foreach ($rows as $r) {
    $p = trim((string)$r['parent_name']);
    if ($p === '') {
        continue;
    }
    if (!isset($parentNameToId[$p])) {
        $id = count($nodes) + 1;
        $meta = $parentMeta[$p] ?? [];
        $parentNameToId[$p] = $id;
        $nodes[$id] = [
            'id' => $id,
            'parent_id' => null,
            'name' => $p,
            'slug' => '',
            'description' => $meta['description'] ?? null,
            'meta_title' => $meta['meta_title'] ?? null,
            'meta_description' => $meta['meta_description'] ?? null,
            'image_path' => $meta['image_path'] ?? null,
            'is_active' => (int)($meta['is_active'] ?? 1),
            'sort_order' => (int)($meta['sort_order'] ?? 0),
        ];
    }
}

// 2) Create children
foreach ($rows as $r) {
    $name = (string)$r['name'];
    $parentName = trim((string)$r['parent_name']);
    $parentId = $parentName === '' ? null : ($parentNameToId[$parentName] ?? null);
    $key = ($parentId === null ? 'NULL' : (string)$parentId) . '>' . $name;

    if (isset($childrenKeyToId[$key])) {
        // Merge: keep first occurrence; ignore duplicates
        continue;
    }

    $id = count($nodes) + 1;
    $childrenKeyToId[$key] = $id;
    $nodes[$id] = [
        'id' => $id,
        'parent_id' => $parentId,
        'name' => $name,
        'slug' => (string)$r['slug'],
        'description' => $r['description'] ?? null,
        'meta_title' => $r['meta_title'] ?? null,
        'meta_description' => $r['meta_description'] ?? null,
        'image_path' => $r['image_path'] ?? null,
        'is_active' => (int)($r['is_active'] ?? 1),
        'sort_order' => (int)($r['sort_order'] ?? 0),
    ];
}

// 2b) Fill missing parent SEO fields from children when root metadata rows are absent.
$childrenByParent = [];
foreach ($nodes as $node) {
    if ($node['parent_id'] !== null) {
        $childrenByParent[(int)$node['parent_id']][] = $node;
    }
}
foreach ($nodes as $id => $node) {
    if ($node['parent_id'] !== null) {
        continue;
    }
    $children = $childrenByParent[(int)$id] ?? [];
    if (count($children) === 0) {
        continue;
    }
    if (($node['meta_title'] ?? null) === null) {
        $nodes[$id]['meta_title'] = truncateUtf8($node['name'] . ' One Gram Gold Jewellery', 255);
    }
    if (($node['meta_description'] ?? null) === null) {
        $childNames = [];
        foreach ($children as $c) {
            $childNames[] = $c['name'];
        }
        $nodes[$id]['meta_description'] = truncateUtf8(
            $node['name'] . ' categories: ' . implode(', ', $childNames),
            500
        );
    }
}

// 3) Generate INSERT values
$insertRows = [];
foreach ($nodes as $node) {
    $id = (int)$node['id'];
    $name = (string)$node['name'];
    $parentSql = $node['parent_id'] === null ? 'NULL' : (string)(int)$node['parent_id'];

    $slug = trim((string)$node['slug']);
    if ($slug === '') {
        // if child under parent, prefer parent-name + child-name to reduce collisions
        if ($node['parent_id'] !== null) {
            $parentName = $nodes[(int)$node['parent_id']]['name'] ?? '';
            $slug = slugify($parentName . '-' . $name);
        } else {
            $slug = slugify($name);
        }
    } else {
        $slug = slugify($slug);
    }
    if ($slug === '') {
        $slug = 'category-' . $id;
    }

    $baseSlug = $slug;
    if (isset($usedSlugs[$baseSlug])) {
        $usedSlugs[$baseSlug]++;
        $slug = $baseSlug . '-' . $usedSlugs[$baseSlug];
    } else {
        $usedSlugs[$baseSlug] = 1;
    }

    $insertRows[] = "("
        . $id . ", "
        . $parentSql . ", "
        . sqlString($name) . ", "
        . sqlString($slug) . ", "
        . sqlNullableString($node['description'] ?? null) . ", "
        . sqlNullableString($node['meta_title'] ?? null) . ", "
        . sqlNullableString($node['meta_description'] ?? null) . ", "
        . sqlNullableString($node['image_path'] ?? null) . ", "
        . (int)($node['is_active'] ?? 1) . ", "
        . (int)($node['sort_order'] ?? 0) . ", "
        . sqlNullableString($now) . ", "
        . sqlNullableString($now)
        . ")";
}

$sql = <<<SQL
-- Generated from {$xlsxPath} by database/scripts/xlsx_to_categories_sql.php
-- mysql dbname < database/categories.sql

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(500) DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_slug_index` (`slug`),
  KEY `categories_parent_id_index` (`parent_id`),
  KEY `idx_categories_parent_active` (`parent_id`,`is_active`),
  KEY `idx_categories_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `meta_title`, `meta_description`, `image_path`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
SQL;

$sql .= "\n" . implode(",\n", $insertRows) . ";\n\n";
$sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";

if (file_put_contents($sqlPath, $sql) === false) {
    fwrite(STDERR, "Error: Could not write: {$sqlPath}\n");
    exit(1);
}

echo "Generated: {$sqlPath} (" . count($insertRows) . " categories";
echo ")\n";

/**
 * Reads the first worksheet and returns rows as a 2D array of strings.
 *
 * This is a lightweight XLSX reader that supports shared strings and basic numeric cells.
 */
function readXlsxFirstSheet(string $path): array
{
    $zip = new ZipArchive();
    $ok = $zip->open($path);
    if ($ok !== true) {
        throw new RuntimeException("Could not open XLSX as ZIP (code {$ok}).");
    }

    $sharedStrings = [];
    $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
    if ($sharedXml !== false) {
        $sx = simplexml_load_string($sharedXml);
        if ($sx !== false) {
            foreach ($sx->si as $si) {
                // shared string can be in <t> or rich text <r><t>
                if (isset($si->t)) {
                    $sharedStrings[] = (string)$si->t;
                } else {
                    $parts = [];
                    foreach ($si->r as $r) {
                        if (isset($r->t)) {
                            $parts[] = (string)$r->t;
                        }
                    }
                    $sharedStrings[] = implode('', $parts);
                }
            }
        }
    }

    // Use sheet1 by convention; this matches most simple exports.
    $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
    if ($sheetXml === false) {
        $zip->close();
        throw new RuntimeException("Missing xl/worksheets/sheet1.xml in XLSX.");
    }
    $zip->close();

    $sx = simplexml_load_string($sheetXml);
    if ($sx === false || !isset($sx->sheetData)) {
        throw new RuntimeException("Invalid sheet XML.");
    }

    $grid = [];
    foreach ($sx->sheetData->row as $row) {
        $cells = [];
        $maxCol = 0;
        foreach ($row->c as $c) {
            $ref = (string)$c['r']; // e.g. A1
            $colLetters = preg_replace('/[^A-Z]/', '', strtoupper($ref));
            $colIndex = lettersToIndex($colLetters); // 0-based
            $maxCol = max($maxCol, $colIndex);

            $t = (string)$c['t']; // 's' for shared string
            $v = isset($c->v) ? (string)$c->v : '';
            if ($t === 's') {
                $idx = is_numeric($v) ? (int)$v : -1;
                $cells[$colIndex] = $idx >= 0 && isset($sharedStrings[$idx]) ? $sharedStrings[$idx] : '';
            } elseif ($t === 'inlineStr' && isset($c->is->t)) {
                $cells[$colIndex] = (string)$c->is->t;
            } else {
                $cells[$colIndex] = $v;
            }
        }

        // normalize to dense row up to maxCol
        $dense = [];
        for ($i = 0; $i <= $maxCol; $i++) {
            $dense[$i] = isset($cells[$i]) ? (string)$cells[$i] : '';
        }
        $grid[] = $dense;
    }

    return $grid;
}

function lettersToIndex(string $letters): int
{
    $letters = strtoupper($letters);
    $n = 0;
    for ($i = 0; $i < strlen($letters); $i++) {
        $n = $n * 26 + (ord($letters[$i]) - 64);
    }
    return max(0, $n - 1);
}

function buildHeaderIndexMap(array $headerRow): array
{
    $map = [];
    foreach ($headerRow as $i => $cell) {
        $key = normalizeHeader((string)$cell);
        if ($key === '') {
            continue;
        }
        // first occurrence wins
        if (!isset($map[$key])) {
            $map[$key] = (int)$i;
        }
    }
    return $map;
}

function normalizeHeader(string $h): string
{
    $h = trim($h);
    $h = preg_replace('/\s+/', ' ', $h);
    $h = strtolower($h);
    $h = str_replace(['-', '.', '/'], ' ', $h);
    $h = preg_replace('/\s+/', '_', $h);
    return $h ?? '';
}

function findHeaderIndex(array $headerMap, array $candidates): ?int
{
    foreach ($candidates as $c) {
        $k = normalizeHeader((string)$c);
        if (isset($headerMap[$k])) {
            return (int)$headerMap[$k];
        }
    }
    return null;
}

function isRowBlank(array $row): bool
{
    foreach ($row as $cell) {
        if (trim((string)$cell) !== '') {
            return false;
        }
    }
    return true;
}

function normalizeNullableText(mixed $v): ?string
{
    $s = trim((string)$v);
    if ($s === '' || strtolower($s) === 'null') {
        return null;
    }
    return $s;
}

function truncateUtf8(string $s, int $maxChars): string
{
    if ($maxChars <= 0) {
        return '';
    }
    if (function_exists('mb_strlen') && function_exists('mb_substr')) {
        if (mb_strlen($s, 'UTF-8') <= $maxChars) {
            return $s;
        }
        return mb_substr($s, 0, $maxChars, 'UTF-8');
    }
    // Fallback (byte-based)
    if (strlen($s) <= $maxChars) {
        return $s;
    }
    return substr($s, 0, $maxChars);
}

function parseBoolLike(mixed $v): bool
{
    $s = strtolower(trim((string)$v));
    if ($s === '') {
        return false;
    }
    if (in_array($s, ['1', 'true', 'yes', 'y', 'active', 'enabled', 'on'], true)) {
        return true;
    }
    if (in_array($s, ['0', 'false', 'no', 'n', 'inactive', 'disabled', 'off'], true)) {
        return false;
    }
    // Numeric fallback
    if (is_numeric($s)) {
        return ((float)$s) !== 0.0;
    }
    return false;
}

function parseIntLike(mixed $v, int $default = 0): int
{
    $s = trim((string)$v);
    if ($s === '' || !is_numeric($s)) {
        return $default;
    }
    return (int)round((float)$s);
}

function slugify(string $text): string
{
    $text = trim($text);
    $text = preg_replace('/[^a-z0-9]+/i', '-', $text);
    $text = trim((string)$text, '-');
    return strtolower($text);
}

function sqlString(string $s): string
{
    // MySQL string literal escaping
    $s = str_replace(["\\", "\0", "\n", "\r", "\t", "\x1a", "'"], ["\\\\", "\\0", "\\n", "\\r", "\\t", "\\Z", "\\'"], $s);
    return "'" . $s . "'";
}

function sqlNullableString(?string $s): string
{
    if ($s === null) {
        return 'NULL';
    }
    return sqlString($s);
}