<?php
/**
 * Converts database/categories.csv into database/categories.sql
 *
 * CSV columns (order or header name flexible):
 *   name, parent (parent_ca / parent_category_name), sort (sort_orde / sort_order), is_active
 *
 * Run from project root: php database/scripts/csv_to_categories_sql.php
 */

$projectRoot = dirname(__DIR__, 2);
$csvPath = $projectRoot . DIRECTORY_SEPARATOR . 'database' . DIRECTORY_SEPARATOR . 'categories.csv';
$sqlPath = $projectRoot . DIRECTORY_SEPARATOR . 'database' . DIRECTORY_SEPARATOR . 'categories.sql';

if (!is_readable($csvPath)) {
    fwrite(STDERR, "Error: categories.csv not found at: {$csvPath}\n");
    exit(1);
}

$handle = fopen($csvPath, 'r');
if ($handle === false) {
    fwrite(STDERR, "Error: Could not open CSV file.\n");
    exit(1);
}

// Read header and map column indices (supports name, parent_ca/parent_category_name, sort_orde/sort_order, is_active)
$first = fgets($handle);
$first = str_replace("\xEF\xBB\xBF", '', $first);
$header = str_getcsv($first);
$header = array_map('trim', $header);

$colName = null;
$colParent = null;
$colSort = null;
$colActive = null;
foreach ($header as $i => $h) {
    $h = strtolower($h);
    if (strpos($h, 'name') === 0 || $h === 'name') {
        $colName = $i;
    } elseif (strpos($h, 'parent') !== false) {
        $colParent = $i;
    } elseif (strpos($h, 'sort') !== false) {
        $colSort = $i;
    } elseif (strpos($h, 'active') !== false) {
        $colActive = $i;
    }
}
if ($colName === null) {
    fwrite(STDERR, "Error: CSV must have a 'name' column. Headers: " . implode(', ', $header) . "\n");
    exit(1);
}
if ($colParent === null) {
    $colParent = 1;
}
if ($colSort === null) {
    $colSort = 2;
}
if ($colActive === null) {
    $colActive = 3;
}

$rows = [];
while (($row = fgetcsv($handle, 0, ',')) !== false) {
    $row = array_map('trim', $row);
    if (empty(array_filter($row))) {
        continue;
    }
    $name = $row[$colName] ?? '';
    if ($name === '') {
        continue;
    }
    $rows[] = [
        'name' => $name,
        'parent_category_name' => isset($row[$colParent]) ? (string) $row[$colParent] : '',
        'sort_order' => isset($row[$colSort]) && is_numeric($row[$colSort]) ? (int) $row[$colSort] : 0,
        'is_active' => isset($row[$colActive]) ? (in_array(strtolower((string) $row[$colActive]), ['1', 'true', 'yes', 'active'], true)) : true,
    ];
}
fclose($handle);

$nameToId = [];
$usedSlugs = [];
$insertRows = [];
$now = date('Y-m-d H:i:s');
$skipped = 0;

foreach ($rows as $r) {
    $parentId = null;
    $parentName = trim($r['parent_category_name']);
    if ($parentName !== '') {
        if (!isset($nameToId[$parentName])) {
            fwrite(STDERR, "Warning: Parent '{$parentName}' not found (must appear before child). Skipping: {$r['name']}\n");
            $skipped++;
            continue;
        }
        $parentId = $nameToId[$parentName];
    }

    $id = count($insertRows) + 1;
    $nameToId[$r['name']] = $id;

    $slug = slugify($r['name']);
    if ($slug === '') {
        $slug = 'category-' . $id;
    }
    if (isset($usedSlugs[$slug])) {
        $usedSlugs[$slug]++;
        $slug = $slug . '-' . $usedSlugs[$slug];
    } else {
        $usedSlugs[$slug] = 1;
    }

    $parentSql = $parentId === null ? 'NULL' : (int) $parentId;
    $isActive = $r['is_active'] ? 1 : 0;
    $sortOrder = (int) $r['sort_order'];
    $nameEsc = addslashes($r['name']);
    $slugEsc = addslashes($slug);

    $insertRows[] = "({$id}, {$parentSql}, '{$nameEsc}', '{$slugEsc}', NULL, {$isActive}, {$sortOrder}, '{$now}', '{$now}')";
}

$nextId = count($insertRows) + 1;

$sql = <<<SQL
-- Generated from database/categories.csv by database/scripts/csv_to_categories_sql.php
-- Run this file to create/truncate categories and load data (e.g. 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,
  `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`, `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 categories.sql.\n");
    exit(1);
}

echo "Generated: {$sqlPath} (" . count($insertRows) . " categories";
if ($skipped > 0) {
    echo ", {$skipped} skipped (parent not found)";
}
echo ")\n";

function slugify(string $name): string
{
    $slug = preg_replace('/[^a-z0-9]+/i', '-', $name);
    $slug = trim($slug, '-');
    return strtolower($slug);
}