<?php

declare(strict_types=1);

namespace PpPhp\Core;

/**
 * Central project-catalog metadata that is independent of editable project state.
 *
 * The table is created lazily for upgraded installations and by the installer
 * for new installations. Project files remain the source of truth for RAD state.
 */
final class ProjectCatalog
{
    private static bool $schemaReady = false;

    public const COLORS = [
        '#2563eb', '#4f46e5', '#7c3aed', '#db2777',
        '#0891b2', '#0f766e', '#059669', '#d97706', '#dc2626', '#475569',
    ];

    public const ICONS = [
        'folder2-open', 'window-stack', 'grid-1x2', 'database', 'diagram-3',
        'briefcase', 'people', 'cart3', 'calendar3', 'rocket-takeoff',
    ];

    public static function ensureSchema(): void
    {
        if (self::$schemaReady) return;

        DB::Init();
        DB::SetConnection();
        DB::Exec(self::schemaSql());
        if (DB::LastError() !== '') {
            throw new \RuntimeException('Could not prepare project catalog metadata: ' . DB::LastError());
        }
        self::$schemaReady = true;
    }

    public static function schemaSql(): string
    {
        return "CREATE TABLE IF NOT EXISTS `project_catalog` (
            `project_id` INT NOT NULL PRIMARY KEY,
            `is_pinned` TINYINT(1) NOT NULL DEFAULT 0,
            `archived_at` DATETIME DEFAULT NULL,
            `last_opened_at` DATETIME DEFAULT NULL,
            `color` VARCHAR(20) NOT NULL DEFAULT '#4f46e5',
            `icon` VARCHAR(50) NOT NULL DEFAULT 'folder2-open',
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT `fk_project_catalog_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
            INDEX `idx_project_catalog_pinned` (`is_pinned`),
            INDEX `idx_project_catalog_archived` (`archived_at`),
            INDEX `idx_project_catalog_opened` (`last_opened_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    }

    /** @return array<int,array<string,mixed>> keyed by project id */
    public static function metadataFor(array $projectIds): array
    {
        self::ensureSchema();
        $ids = array_values(array_unique(array_filter(array_map('intval', $projectIds), static fn(int $id): bool => $id > 0)));
        if ($ids === []) return [];

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $rows = DB::FetchAll("SELECT * FROM `project_catalog` WHERE `project_id` IN ({$placeholders})", $ids) ?: [];
        $result = [];
        foreach ($rows as $row) {
            $result[(int)$row['project_id']] = self::normalize($row);
        }
        return $result;
    }

    public static function defaults(int $projectId): array
    {
        return [
            'project_id' => $projectId,
            'is_pinned' => false,
            'archived_at' => null,
            'last_opened_at' => null,
            'color' => '#4f46e5',
            'icon' => 'folder2-open',
        ];
    }

    public static function setPinned(int $projectId, bool $pinned): array
    {
        self::ensureRecord($projectId);
        self::updateOrFail(['is_pinned' => $pinned ? 1 : 0], $projectId);
        return self::one($projectId);
    }

    public static function setArchived(int $projectId, bool $archived): array
    {
        self::ensureRecord($projectId);
        self::updateOrFail(['archived_at' => $archived ? date('Y-m-d H:i:s') : null], $projectId);
        return self::one($projectId);
    }

    public static function updatePresentation(int $projectId, string $color, string $icon): array
    {
        self::ensureRecord($projectId);
        $color = strtolower(trim($color));
        $icon = strtolower(trim($icon));
        if (!in_array($color, self::COLORS, true)) $color = '#4f46e5';
        if (!in_array($icon, self::ICONS, true)) $icon = 'folder2-open';
        self::updateOrFail(['color' => $color, 'icon' => $icon], $projectId);
        return self::one($projectId);
    }

    public static function touchOpened(int $projectId): void
    {
        self::ensureRecord($projectId);
        self::updateOrFail(['last_opened_at' => date('Y-m-d H:i:s')], $projectId);
    }

    public static function remove(int $projectId): void
    {
        self::ensureSchema();
        DB::Delete('project_catalog', ['project_id' => $projectId]);
    }

    private static function ensureRecord(int $projectId): void
    {
        self::ensureSchema();
        $result = DB::Query(
            "INSERT INTO `project_catalog` (`project_id`) VALUES (?) ON DUPLICATE KEY UPDATE `project_id` = VALUES(`project_id`)",
            [$projectId]
        );
        if ($result === false) {
            throw new \RuntimeException('Could not initialize project catalog metadata: ' . DB::LastError());
        }
    }

    private static function updateOrFail(array $values, int $projectId): void
    {
        if (DB::Update('project_catalog', $values, ['project_id' => $projectId]) === false) {
            throw new \RuntimeException('Could not update project catalog metadata: ' . DB::LastError());
        }
    }

    private static function one(int $projectId): array
    {
        $rows = DB::Select('project_catalog', ['project_id' => $projectId]);
        return isset($rows[0]) ? self::normalize($rows[0]) : self::defaults($projectId);
    }

    private static function normalize(array $row): array
    {
        return [
            'project_id' => (int)($row['project_id'] ?? 0),
            'is_pinned' => !empty($row['is_pinned']),
            'archived_at' => ($row['archived_at'] ?? null) ?: null,
            'last_opened_at' => ($row['last_opened_at'] ?? null) ?: null,
            'color' => in_array(strtolower((string)($row['color'] ?? '')), self::COLORS, true)
                ? strtolower((string)$row['color']) : '#4f46e5',
            'icon' => in_array(strtolower((string)($row['icon'] ?? '')), self::ICONS, true)
                ? strtolower((string)$row['icon']) : 'folder2-open',
        ];
    }
}
