<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;

class ImportCategories extends Command
{
    protected $signature = 'import:categories {path=database/artworks_categories.csv}';

    protected $description = 'Import categories from legacy CSV into categories table';

    public function handle(): int
    {
        $path = base_path($this->argument('path'));

        if (!file_exists($path)) {
            $this->error("CSV file not found: {$path}");
            return 1;
        }

        $file = new \SplFileObject($path);
        $file->setFlags(\SplFileObject::READ_CSV | \SplFileObject::SKIP_EMPTY);

        $header = null;
        $rows = [];

        foreach ($file as $index => $row) {
            if ($row === [null] || $row === false) {
                continue;
            }
            if ($index === 0) {
                $header = $row;
                continue;
            }

            // normalize row length
            if (!is_array($row)) {
                continue;
            }

            $rows[] = $row;
        }

        // build a map of rows by category id to allow ancestor lookups
        $map = [];
        foreach ($rows as $r) {
            $rid = isset($r[0]) ? (int)$r[0] : null;
            if ($rid) {
                $map[$rid] = $r;
            }
        }

        // Temporarily disable foreign key checks to allow inserting parents out of order
        DB::statement('SET FOREIGN_KEY_CHECKS=0');

        // truncate categories table to start fresh
        DB::table('categories')->truncate();

        DB::beginTransaction();
        try {
            $this->info('Inserting categories...');

            // helper to walk ancestor chain and find a valid content_type_id
            $findAncestorContentType = function ($startRoot) use ($map) {
                $seen = [];
                $cur = $startRoot;
                while ($cur && isset($map[$cur]) && !in_array($cur, $seen, true)) {
                    $seen[] = $cur;
                    $ancestor = $map[$cur];
                    $candidate = isset($ancestor[7]) ? (int)$ancestor[7] : null;
                    if (!empty($candidate)) {
                        // verify the content type exists in DB
                        if (DB::table('content_types')->where('id', $candidate)->exists()) {
                            return $candidate;
                        }
                    }
                    $cur = isset($ancestor[8]) ? (int)$ancestor[8] : null;
                }
                return null;
            };

            // helper to collect ancestor chain rows for debugging dump
            $collectAncestorChain = function ($startRoot) use ($map) {
                $chain = [];
                $seen = [];
                $cur = $startRoot;
                while ($cur && isset($map[$cur]) && !in_array($cur, $seen, true)) {
                    $seen[] = $cur;
                    $r = $map[$cur];
                    $chain[] = [
                        'id' => $cur,
                        'name' => $r[1] ?? null,
                        'section_id' => isset($r[7]) ? (int)$r[7] : null,
                        'rootid' => isset($r[8]) ? (int)$r[8] : null,
                        'raw' => $r,
                    ];
                    $cur = isset($r[8]) ? (int)$r[8] : null;
                }
                return $chain;
            };

            foreach ($rows as $row) {
                // CSV format: category_id,category_name,num_artworks,description,official_webpage,picture,views,section_id,rootid
                $origId = isset($row[0]) ? (int)$row[0] : null;
                $name = isset($row[1]) ? trim($row[1]) : '';
                $description = isset($row[3]) && $row[3] !== '' ? $row[3] : null;
                $image = isset($row[5]) && $row[5] !== '' ? $row[5] : null;
                $contentTypeId = isset($row[7]) ? (int)$row[7] : null;
                $rootId = isset($row[8]) ? (int)$row[8] : null;

                // If the CSV `rootid` corresponds to a content type, it's a root for that content type => parent_id NULL
                if (!empty($rootId) && DB::table('content_types')->where('id', $rootId)->exists()) {
                    $parentId = null;
                } else {
                    // If content_type_id == parent_id then parent_id should be NULL
                    $parentId = ($contentTypeId === $rootId || $rootId === 0) ? null : $rootId;
                }

                // resolve missing/invalid content_type_id from ancestor chain (parent/root)
                $ctExists = !empty($contentTypeId) && DB::table('content_types')->where('id', $contentTypeId)->exists();
                if (!$ctExists) {
                    if (!empty($rootId)) {
                        $resolved = $findAncestorContentType($rootId);
                        if ($resolved) {
                            $contentTypeId = $resolved;
                            $this->info("Resolved content_type_id for category {$origId} from ancestor: {$contentTypeId}");
                        }
                    }
                }

                // after resolution, verify content_type exists
                if (empty($contentTypeId) || !DB::table('content_types')->where('id', $contentTypeId)->exists()) {
                    $this->error("Missing content_type_id for category {$origId}. Dumping data and aborting import.");

                    $dump = [
                        'row' => $row,
                        'resolved_root' => $rootId,
                        'ancestor_chain' => $rootId ? $collectAncestorChain($rootId) : [],
                    ];

                    $this->line(json_encode($dump, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));

                    throw new \RuntimeException('Aborting import: unresolved content_type_id for category ' . $origId);
                }

                $slug = Str::slug($name) ?: 'cat-' . $origId;

                // ensure uniqueness for slug within content_type
                $existing = DB::table('categories')
                    ->where('content_type_id', $contentTypeId)
                    ->where('slug', $slug)
                    ->exists();

                if ($existing) {
                    $slug = $slug . '-' . $origId;
                }

                $now = now();

                $data = [
                    'id' => $origId,
                    'content_type_id' => $contentTypeId,
                    'parent_id' => $parentId,
                    'name' => $name,
                    'slug' => $slug,
                    'description' => $description,
                    'image' => $image,
                    'is_active' => true,
                    'sort_order' => 0,
                    'created_at' => $now,
                    'updated_at' => $now,
                ];

                DB::table('categories')->insert($data);
                $this->line("Inserted category {$origId}: {$name}");
            }

            DB::commit();
            $this->info('Import completed.');
            return 0;
        } catch (\Throwable $e) {
            DB::rollBack();
            $this->error('Import failed: ' . $e->getMessage());
            return 1;
        } finally {
            // re-enable foreign key checks
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        }
    }
}
